AGGR not working

[hopkinsc]

Hi all,

I have a measure where i am trying to compare a list of ID's in a previous months report against a current month report and if there are any that do not appear in the current report then i want to flag it with 1. This is to be dynamic, so it needs to be done in the chart. 

I have this measure working when i have the ID as a dimension. I don't want the ID showing in the chart, so i need to use the AGGR function to see the total (a sum of all the 1's) but I can't get it to work. 

I have attached a sample, any help would be appreciated. 

I  have 2 measures, both give the same result, they are just written in a different way - I was trying different methods to try and get it working. If you select Aug-2023, I am looking for 9 to be the total. You will see 9 rows flagged with 1, I just want to sum those rows - but can't use the SUM in the totals function in the properties as this won't work when the ID is not in the chart.

[hopkinsc]

I had a feeling it could be to do with the ONLY() function 

It can't be done in the script, as it needs to be dynamic, the user needs to be able to select any monthyear combination, not just current/previous. 

I think this is why it's so difficult. 

[Aditya_Chitale]

Its not difficult just a bit tricky. Maybe it could be achieved using some if() conditions along with aggr(). Allow me some time. Will get back to you once done with some personal work.

Regards,

Aditya

[hopkinsc]

Appreciate your help 🙂 

[hopkinsc]

I still don't have a solution to this, any help would be appreciated. Thanks everyone. 

[Aditya_Chitale · Accepted solution]

Hey @hopkinsc ,

Sorry for the late response. I have been busy for past 4-5 days and couldn't find time to take a  look at your issue.

Hope you have found the solution by now. If not, please find below solution I applied to get total count of null IDs which were present in past month but not current month on the  basis of OPCO field.

Expression used:

count({<ReportingYearPaymentFilter_Flag = {1},DailyRatesMonthYear={'$(vLastMonth)','$(vSelectedMonth)'}>}distinct GroundLeaseID)
-
count({<ReportingYearPaymentFilter_Flag = {1},DailyRatesMonthYear={'$(vSelectedMonth)'}>}distinct GroundLeaseID)

Output:

Hope this helps.

Regards,

Aditya

[hopkinsc]

Hi Aditya,

Thanks very much for this,  It's working. 

And looking at the measure, it makes sense what you have done, I don't know why i didn't think of that! 

Thanks again

[raphael_goldstein]

Hi there.

I'm a new user so sorry for replying so late and reopening this issue.

I hope by answering I can help others who wander into this thread.

What worked for me was wrapping the calculation of the flag with Num(Num#(....)).

For some reason this fixed it for me.

Raphael.