topic Distinct pre Aggregate data on a field based on Year and Month combination. in QlikView

Distinct pre Aggregate data on a field based on Year and Month combination.

luxlux80 — Tue, 02 May 2017 15:57:21 GMT

We have an Qlik Sense application with 2 Million distinct <Employee> in a 6 Million rows of Fact Table and need to perform several calculations that using <Count Distinct Employee> in the denominator. E.g. (<Total Amount> /<count distinct Employee>)

Application Year and Month are the Filters.

Sample Qlik sense application (YrlyMonthlyDailyDistinctEmpCount) with sample data are as attached.

Please let me know the solution to pre aggregate <Count Distinct Employee> based on Year and Month combination.

Expecting Pre aggregated Distinct Employee Count for the below filter selection scenario

when only

one year selected

two or both year selected

Single Month selected

Multiple months Selected

Single Month and a year selected

Multiple months and a year Selected

Single Month and both years selected

Multiple months and both years Selected

Re: Distinct pre Aggregate data on a field based on Year and Month combination.

sunny_talwar — Tue, 02 May 2017 17:02:18 GMT

Not sure I understand the output you are looking to get, would you be able to provide the expected output for the sample attached?

Re: Distinct pre Aggregate data on a field based on Year and Month combination.

neelamsaroha157 — Tue, 02 May 2017 17:44:44 GMT

Are you looking for something like this.

Re: Distinct pre Aggregate data on a field based on Year and Month combination.

luxlux80 — Tue, 02 May 2017 20:12:04 GMT

Expecting Pre aggregated Distinct Employee Count for the below filter selection scenario

when only

one year selected

two or both year selected

Single Month selected

Multiple months Selected

Single Month and a year selected

Multiple months and a year Selected

Single Month and both years selected

Multiple months and both years Selected