Display pre aggregated fields in KPI

[prithviKamath]

Hello,

I am a beginner in Qlik Sense. Since I am working on a huge dataset, I am aggregating the values before importing it in Qlik Sense to improve the  performance. Now, I wish to only display a pre-aggregated field in a KPI chart. Below are the options that I get while adding a new measure to the KPI chart.

If I forcefully edit the expression to remove the aggregation, it does not display any value.

My aggregated table now looks like this:

So I plan to have a Filter panes for Year and Gender and KPI chart or Patient Count. 

Can anybody kindly suggest a work around?

Any help will be greatly appreciated! Thanks!

[Steven_Haught]

Are you trying to have the KPI update when a dimension is selected? 

If so you need to do the aggregation. Sum(patientcount) Upon selection of a dimension, say doctor, it should display the correct sum of patient count.

You will likely not want to display the TOTAL patient count unless a selection is made. If so use data handling with a GetSelectedCount(yourdimfield,1).

[prithviKamath]

Thanks Steven for your time.

I have edited my question to include the aggregated table. I am looking to only display a pre calculated field in the KPI chart without any aggregations.

[Kushal_Chawda]

when you say pre calculated field in KPI means? KPI is dimensionless object you have to perform aggregation to display value.

[tm_burgers]

If your goal is to have the kpi display the total count of patients based on a filter pane, then based on your sample table you will have to exclude the data not associated with year.

Sum({<  year={">2000"}>}   Patientcount )

This will show accurate overall total with no selections, and update based on selections.

EDIT: also, huge dataset is what qlik is great at, why pre-aggregate? Or use a qlik QVD to aggregate and then use the QVD for the business App. Just my two cents. 

[prithviKamath]

Hi @tm_burgers,

Thanks for your time!

My goal is to have KPI chart display the Patient Count corresponding to each filter selection, not the Sum/Total of Patient Count.

Unfortunately, your code does not display accurate values. Please find below some test cases:

Case	Displayed Value	Expected Value
No selection in Year or Gender	390	250
Only Female selected	240	150
Male and 2016 selected	150	50

Thanks again for your help.

[martinpohl]

one thing is what you expected, the other thing ate your numbers.

40+50+60 = 150 not 100

and if it is 100, how it is calculated?

So, aggregated values in datamodel is nessesary on DWH but not in Qlik.

Also, you have 100 and 150 in total for two genders. Your expression only is a field, no aggregation. 

So what should Qlik do

100+150

100*150

100-150

avg(100,150)

?

Hope you understand

[tm_burgers]

I'm not sure if it is your data table that is wrong; or your expected results; but the KPI is doing its job. 

In the table you have provided - are the Male and Female values without a year supposed to be included in the totals? 

You asked for no selections to show just the total of 250 = Year(null) Male and Female; but no selections would sum your entire dataset. No selections based on your sample data does = 390.

Is this because the Female and Male counts that are aggregated with Null Year are a count of Unique PatientIDs; whereas the yearly data is a unique count within that year?

If this is the case; and you are trying to eliminate the overlap in PatientIDs over multiple years - it would maybe be easier to use the full data set and then adjust your Set expression to "distinct"

[prithviKamath]

Hi @martinpohl,

I want Qlik to only display the number as is! without any aggregations.

[prithviKamath]

Hi @tm_burgers ,

You got it absolutely right! 'Patient Count' represents 'Distinct Patient Counts' where patients are either distinctly counted over the year or across the entire time period. Since my original data is around 40GB, using Count (Distinct PatientID) takes a few minutes and reduces the performance. The expectation is to reduce this lag and have the dashboard be responsive. Hence I have moved the aggregation part downstream and all I expect from Qlik is to display the counts as is (without any aggregation) based on the selected filter.

Hope this helps!

Kindly let me know your thoughts!