topic Re: Counting at the back end in App Development

Counting at the back end

LoKi_asterix — Tue, 10 Dec 2024 09:54:26 GMT

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?

Re: Counting at the back end

Kushal_Chawda — Tue, 10 Dec 2024 10:02:20 GMT

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

Re: Counting at the back end

LoKi_asterix — Tue, 10 Dec 2024 10:31:16 GMT

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

Re: Counting at the back end

krishna20 — Tue, 17 Dec 2024 09:10:29 GMT

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.

Re: Counting at the back end

Aditya_Chitale — Tue, 17 Dec 2024 09:53:56 GMT

@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

Re: Counting at the back end

LoKi_asterix — Wed, 25 Dec 2024 08:04:12 GMT

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