Problem with AGGR not capturing the right time window

[#1]

Hello,

I have a problem with AGGR not behaving like I expect.

I did not see the problem sooner because if I look at the data from Q314 to Q315, the results are OK.

However If I look at more ancient data as I go back in time the results starts to be more and more different than what I expect.

I may misunderstand the way AGGR woks.

The bellow example is a formula that works exactly as I expect for all the history present in the data.

Sum(

{$<Currency_ISO_CD=, Unit_type=, Filter_Product=

, [key_MRSD_Week_Delq_Grouped_CurrentWeek] = {">=$(vP1Start) <=$(=vP1End)"}>} [BB_Net_USD]

)

The bellow formula is not working, It brings in some data which comes from older periods than the periods currently selected in vP1 and vP2. What I try to do is to get the data present in both P1 and P2, aggregated by the combination of keys given by the ComparisonType variable, i.e. Key_Product as an exemple. It looks like that aggr is using the comparison type to pull data regardless of the time selection made above

Sum(Aggr(

if(

SUM({<[key_MRSD_Week_Delq_Grouped_CurrentWeek] = {">=$(vP1Start) <=$(=vP1End)"}>} [BB_Net_USD])

* SUM({<[key_MRSD_Week_Delq_Grouped_CurrentWeek] = {">=$(vP2Start) <=$(=vP2End)"}>} [BB_Net_USD]) = 0, 0,

SUM({<[key_MRSD_Week_Delq_Grouped_CurrentWeek] = {">=$(vP1Start) <=$(=vP1End)"}>} [BB_Net_USD])

)

, $(vComparisonType)))

[sunny_talwar · Accepted solution]

Try this:

Sum({<[key_MRSD_Week_Delq_Grouped_CurrentWeek] = {">=$(vP1Start) <=$(=vP1End)"}>}Aggr(

if(

SUM({<[key_MRSD_Week_Delq_Grouped_CurrentWeek] = {">=$(vP1Start) <=$(=vP1End)"}>} [BB_Net_USD])

* SUM({<[key_MRSD_Week_Delq_Grouped_CurrentWeek] = {">=$(vP2Start) <=$(=vP2End)"}>} [BB_Net_USD]) = 0, 0,

SUM({<[key_MRSD_Week_Delq_Grouped_CurrentWeek] = {">=$(vP1Start) <=$(=vP1End)"}>} [BB_Net_USD])

)

, $(vComparisonType)))

[ToniKautto]

Please keep in mind that the dollar expansions are calculated once before the entire expression is evaluated. They will not be evaluated row by row. With the details you have provided it is impossible to tell what you exactly are calculating, since you have expansions of variables that we do not understand the content of.

My suggestion is that you start by validating that the expression actually look the way you expect it to, and then consider how to tweak it to get the expected result.

Set expression and dollar expansion

[#4]

sunindia, the edited formula is indeed selecting data from the expected time window. Not done yet however the total of the computation is still not as expected, actually the total is still the same. I tried to change the Total Mode to sum of rows but it is even worse like that.
I think My best bet is to build and post a small model that shows clearly the issue and take the time to really explain correctly what I am trying to achieve. I cannot just post the model I have it is around 1GB

[#5]

This worked, the other problem I had was due to an incorrect granularity of the data. Now it works exactly like I need it to work.

Thanks for your help