Above function help

[mahitham]

Hi Experts,

Can any one please help me on below requirement. I have the YTD sales in the months like below. From these YTD Sales need to calculate each month actual sales by Current Month -Previous Month sales. But when the previous month has no sales then current month has to show zero for example July has 500 but previous month Jun has no sales so July has to show zero as per expected output.

Please help me on this.

Month       sum(sales)           Expected output

Jan             

Feb

Mar

Apr

May

Jun

July      500                                    0

Aug       700                                   200

Sep       800                                    100

Oct        900                                    100

Nov       1200                                  300

Dec        1400                                 200

Thanks in advance.

[OmarBenSalem]

if(aggr(above(Sum(sales)),Month)=0,0, Sum(sales)-aggr(above(Sum(sales)),Month))

result:

[sunny_talwar]

If month is the only dimension in the chart, why do you need Aggr() here? Why not just use this
If(Above(Sum(sales))= 0, 0, Sum(sales) - Above(Sum(sales)))

Also, I am not sure what the expectation is, but what if Sep's Sum(sales) = 0... would the OP want to see Oct to show 0 or would it still be Oct - Sep?

[OmarBenSalem]

I add the aggr by month so that if the sorting of the table is altered, the result does not change.

see:

Now, I change the sorting order of the Months:

[sunny_talwar]

If that is what the goal is, then why do you need multiple Aggr()? Just use one?

Aggr(If(Above(Sum(sales))= 0, 0, Sum(sales) - Above(Sum(sales))), Month)

I guess, in this simple example, it doesn't matter, but I usually try to keep the usage of Aggr() to its minimum. Because each Aggr() will create its own virtual table slowing down your expression even more.

[OmarBenSalem]

I see. I wasn't aware of this 🙂
Thanks sun

[sunny_talwar]

Your very welcome. and Happy New Year!!