- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
AGGR not working
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.
- Subscribe by Topic:
-
Chart
-
Client Managed
-
Developers
-
expression
-
General Question
-
Set Analysis
-
Visualization
- « Previous Replies
-
- 1
- 2
- Next Replies »
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Appreciate your help 🙂
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I still don't have a solution to this, any help would be appreciated. Thanks everyone.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- « Previous Replies
-
- 1
- 2
- Next Replies »