Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello Qlik Community, I'm struggling to replicate a basic Count(DISTINCT) at the backend.
I'm noticing discrepancies between two approaches:
Scenario 1 works as expected, but doesn't allow for front-end selections.
LOAD ID, Count(DISTINCT ID) as IDToCount
Resident Main
Where Type1 = 'I' and Type2 = 'N' AND Date >= '01/01/2024' AND Date <= '09/12/2024'
Group By ID;
Scenario 2, which includes extra fields, allows selections but produces different results:
LOAD ID, Count(DISTINCT ID) as IDToCount, Max(Date) as Date, MaxString(Type1) as Type1, MaxString(Type2) as Type2
Resident Main;
What’s the best way to align the results while keeping front-end interactivity?
@LoKi_asterix It is always better to use this as a front end expression in charts if you want to keep front end interactivity
@Kushal_Chawda I’m encountering another issue with totals in a pivot table. A basic Count(DISTINCT ID) appears accurate in the table's total row but inflates when exported to Excel. Has anyone experienced this and found a reliable solution?
Hi @LoKi_asterix - Have you checked the nulls in pivot table? Expand all the dimensions (Enable Include Nulls under the Dimenions) and check the totals.
If you want to allow frontend selections, why not apply the expression on frontend itself ?
Also I am not able to understand the purpose of scenerio 2. Perhaps if you could provide a sample data and expected output it could help for better understanding of your requirement.
Regards,
Aditya
I got with working with nested aggregation expression. Thanks, ya'll.