Repeating rows

[fml55555]

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

[raj_aries81]

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

[fml55555]

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!

[Lynn]

This is a relational source, true?

You could try using your original formulas and set the aggregate function to total.

[fml55555]

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!

[Lynn]

This is a relational source, true?

Can you confirm that you are using a relational source?

[fml55555]

Yes, I am using a relational source

[MDXpressor]

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...