Solved: Re: AGGR not working - Qlik Community

hopkinsc · ‎2023-10-05

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.

Aditya_Chitale · ‎2023-10-16

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

View solution in original post

hopkinsc · ‎2023-10-06

Does anyone have any suggestions on this please?

fe-c · ‎2023-10-06

Have you tried using count(true/false) instead sum(1/0) as flags?

hopkinsc · ‎2023-10-06

Hi, Do you mean using 'true' instead of 1 in the IF() statement then wrapping a count(aggr( around it?

That also gives the same result as sum(1)

😞

hopkinsc · ‎2023-10-06

The AGGR breaks the calculation completely. without AGGR() and using the ID as a dimension, I get the 1 against the correct rows, but adding the AGGR around it changes the 1's to 0's.

hopkinsc · ‎2023-10-09

Any more ideas anyone? 🙂

Aditya_Chitale · ‎2023-10-09

can you provide sample data and the expression that you are using ?

Regards,

Aditya

hopkinsc · ‎2023-10-09

Hi, yes, there is a QVF on the original post 🙂

hopkinsc · ‎2023-10-09

Does anyone have any suggestions?

Aditya_Chitale · ‎2023-10-09

I don't think you can do it using only() because only() requires the field to be added in object which you are using to show aggregated data. in your case : ID. You will have to find solution through scripting.

I will need some time to provide you with the same as I am busy with some other work

Regards,

Aditya

AGGR not working

Chart

Client Managed

Developers

expression

General Question

Set Analysis

Visualization