Set expression, aggr issue

[maasool]

I have master measure, which calculates business plan sales per month:
SUM({$<YEAR={$(CY)}>*$<MONTH={$(CM)}>} SALES_BP)

I am using it in a pivot table, where I have product names as dimensions. 

For each product, I want to multiply this master measure. If last year sales>current year sales, if want to multiply it with one value. If last year sales<=current year sales, I want to multiply it with another value.

Problem is, that the formula is not deciding which multiplier to use based on each product, but on total sales. Therefore, it multiplies each product with same multiplier.

I believe I am missing something in either set expression or I should use Aggr somewhere, but I cant' get it working.

[sunny_talwar]

Would you be able to share a sample where  we can see the issue?

[maasool]

This is the formula I am trying to use (simplyfied it a bit, but point stays the same). Problem is, that it is not calculating current year sales/last year sales for every product, but on totals and is then multiplying every product with same multiplier.

IF(SUM({$<YEAR={$(CY)}>*$<MONTH={$(CM)}>} GWP)/SUM({$<YEAR={$(LY)}>*$<MONTH_LY={$(CM)}>} GWP)>1,
SUM({$<YEAR={$(CY)}>*$<MONTH={$(CM)}>} GWP_BP)*$(PT),
SUM({$<YEAR={$(CY)}>*$<MONTH={$(CM)}>} GWP_BP)*$(PT2))

[sunny_talwar]

I am not entirely sure I understand your issue here... Would you be able to show images or share a sample to show your issue?

[maasool]

I understood what the problem was: my formula was working fine, but pivot table totals was not calculating it correctly. It didn't add up sums based on products, but it calculated the total instead based on the formula. Ideas how to correct pivot table total formula?

[sunny_talwar]

Sum(Aggr(Expression, Dimension/s)) should fix it

[maasool]

Sorry, where should I put it in pivot? In addition to business plan, I have data about current year and previous year sales as well in the pivot.

[sunny_talwar]

Replace your expression my friend. You have not been providing enough info to help you in the best possible way. What are your dimensions in the chart? If you can share a sample, it would make all our life easier.

[maasool]

This is my exact formula, which is calculating correctly on product level:

IF(SUM({$<YEAR={$(LY)}>*$<MONTH={$(CM)}>*$<DAY={"<$(=TD)"}>} GWP)/SUM({$<YEAR_LY={$(LY)}>*$<MONTH_LY={$(CM)}>} GWP_LY)>1 OR
SUM({$<YEAR={$(LY)}>*$<MONTH={$(CM)}>*$<DAY={"<$(=TD)"}>} GWP)/SUM({$<YEAR_LY={$(LY)}>*$<MONTH_LY={$(CM)}>} GWP_LY)<0,
SUM({$<YEAR={$(CY)}>*$<MONTH={$(CM)}>} GWP_BP)*$(PT),
SUM({$<YEAR={$(CY)}>*$<MONTH={$(CM)}>} GWP_BP)*SUM({$<YEAR={$(LY)}>*$<MONTH={$(CM)}>*$<DAY={"<$(=TD)"}>} GWP)/SUM({$<YEAR_LY={$(LY)}>*$<MONTH_LY={$(CM)}>} GWP_LY))

Pivot is not calculating totals correctly.  In pivot I have two dimensions: Sales_Channel and Product. I added picture of the pivot for illustration. I have tried using SUM(AGGR(SUM(....),Product, Sales_Channel)) in all and some of the formula lines, but haven't got it working.

[maasool · Accepted solution]

This solved it:

SUM(AGGR(IF(SUM(AGGR(SUM({$<YEAR={$(LY)}>*$<MONTH={$(CM)}>*$<DAY={"<$(=TD)"}>} GWP),Sales_Channel, Producto))/SUM(AGGR(SUM({$<YEAR_LY={$(LY)}>*$<MONTH_LY={$(CM)}>} GWP_LY),Sales_Channel, Producto))>1 OR
SUM(AGGR(SUM({$<YEAR={$(LY)}>*$<MONTH={$(CM)}>*$<DAY={"<$(=TD)"}>} GWP),Sales_Channel, Producto))/SUM(AGGR(SUM({$<YEAR_LY={$(LY)}>*$<MONTH_LY={$(CM)}>} GWP_LY),Sales_Channel, Producto))<0,
SUM(AGGR(SUM({$<YEAR={$(CY)}>*$<MONTH={$(CM)}>} GWP_BP)*$(PT),Sales_Channel,Producto)),
SUM(AGGR(SUM({$<YEAR={$(CY)}>*$<MONTH={$(CM)}>} GWP_BP)*SUM({$<YEAR={$(LY)}>*$<MONTH={$(CM)}>*$<DAY={"<$(=TD)"}>} GWP)
/SUM({$<YEAR_LY={$(LY)}>*$<MONTH_LY={$(CM)}>} GWP_LY),Sales_Channel, Producto))),Sales_Channel,Producto))