Hi Gurus,
I am building a list(s) which has region names on the first column, the second and third column contains sales number from May 2013 and May 2014. The final 2 coulms contain Year till date numbers for 2013 and 2014 respectively. The problem that I am having is that the region names repeat everytime I add a list.
For e.g. If I have three regions
R1
R2
R2
The structure with just one list is
May 2013
R1 Sales 1
R2 Sales 2
R3 Sales 3
The structure with two lists is
May 2013 May 2014
R1 S1
R1 M1
R2 S2
R2 M2
R3 S3
R3 M3
The problem worsens with YTD as there are as many repetations as the number of months.
I have used the following formula in a data item to calculate the month/Year sales number
If (Year = 2013 and Month = May)
(Sales)
else
Null
For YTD
If (Year = 2013 and Month <= May)
(Sales)
else
Null
I think this might work by adding total to the expression, something as below -
total(Case when extract(month,[dateitem])=06 and extract(year,[dateitem])=2013 then [sales] else 0 end) use this for May 2013
total(Case when extract(month,[dateitem])=06 and extract(year,[dateitem])=2014 then [sales] else 0 end) use this for May 2014
Regards
Raj
Hi Raj,
Thank you very much for the reply. It works for the YTD columns, but I still have repeating rows (one for the May 2013 and one for May 2014). The YTD 2014 now shows up in the same row as May 2014 and YTD 2013 in May 2013.
Thank you!
This is a relational source, true?
You could try using your original formulas and set the aggregate function to total.
Hi Lynn,
Thank you for the response. I have tried it for the YTD.
These are the results
Step1
It fills up rows with the same value the (The repetition of the regions continues, instead of many different numbers the rows fill with sum of those numbers...e.g. istead of
1000
2000
300
it shows
1500
1500
1500
Step 2
I tried to group the regions and group the YTD values, This time I got them to be aligned. But the alignment now is
Sales Sales sales Sales
Region name1 May 2013 YTD 2013
Region name1 may 2014 YTD 2014
I want them in single row. I am unable to eliminate the repitition for the May2014 sales
Thank you!
Quote from: Lynn on 19 Jun 2014 09:16:38 AM
This is a relational source, true?
Can you confirm that you are using a relational source?
Yes, I am using a relational source
you didn't by chance set auto aggregation property on the query to 'false' did you? I've done this many time trying to troubleshoot a query full of aggregated data items.
... It's like the blind leading the blind around me sometimes...