Counting at the back end

[LoKi_asterix]

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?

[Kushal_Chawda]

@LoKi_asterix  It is always better to use this as a front end expression in charts if you want to keep front end interactivity

[LoKi_asterix]

@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?

[krishna20]

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.

[Aditya_Chitale]

@LoKi_asterix 

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

[LoKi_asterix]

I got with working with nested aggregation expression. Thanks, ya'll.