Help with a formula!!

[Nemo1]

Hello everyone,

I created a table and one of its columns is made after the formula Concat(Distinct NiSSoo°Ett, ' | ') which gives back results in every row in this fashion (i will give the example for three of the rows). By the way, NY,WA,NJ;WY AND AL are stores.

NY|WA|NJ

WY|WA|NJ

AL|WA|WY

and then there is a column which is total , calculated with the formula sum(total) that calculates the total sales of each of these rows, gives results like this.

6000  

5000

4000

Being 6000 the sum of sales of NY|WA|NJ , 5000 the sum of sales of WY|WA|NJ and 4000 the sum of sales of AL|WA|WY.

I would like to have another column that separates the sales of each of them in the following fashion

3000|2000|1000 

2500|2000|500

1500|1500|1000

Being 3000 the sales of NY, 2000 the sales of WA and 1000 the sales for NJ, and respectively fort he other two rows.

for your understanding, attached you will find an example of what i have right now, and what i would like to get. 

thanks in advance

[Oleg_Troyansky]

Hi,

For this output, you'd need to use the AGGR() function that will concatenate pre-summarized numbers for each store. In the dimensions of the AGGR, use all chart dimensions, in addition to the store code. Something like this:

Concat(

       AGGR( sum(Sales), Dim1, Dim2, Store)

      , '|'

         )

You may need to add the desired number formatting and sorting options within the AGGR(), to ensure that the amounts and the store codes are sorted the same way.

Cheers,

[Qrishna]

Hi - how do you know the split sales of NY|WA|NJ is 3000|2000|1000 ? is thre any other columns that has these numbers and then you are summing up to get the sum 6000 for these 3 states?