Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
LoKi_asterix
Contributor III
Contributor III

Counting at the back end

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?

5 Replies
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
Contributor III
Contributor III
Author

@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
Specialist II
Specialist II

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
Specialist
Specialist

@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
Contributor III
Contributor III
Author

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