topic Re: Filtering on Pre Calculated Data in QlikView

Filtering on Pre Calculated Data

Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I'm new to Qlik and would like to know how I can view the following data created via a GROUPING SETS statement in SQL. This was used because there are many-to-many relationships between the dimensions.

If you're not familiar with GROUPING SETS data, the first row below totals the unique users for one dimension (Country) and the other rows totals the unique users for two dimensions (Country and Device). This is because some users may have more than one device.

Country	Device	Total Unique Users
FRANCE	NULL	50,000
FRANCE	iOS	26,000
FRANCE	Android	25,000

I want the user who selects Country=FRANCE as the only filter to return 50,000 unique users and not a sum of all rows containing Country=FRANCE.

How is this best achieved in Qlik? Or should I not be using precalculated fields at all in Qlik, and instead use the user level data (what happens if this is very large)?

Thanks

Re: Filtering on Pre Calculated Data

sunny_talwar — Tue, 17 May 2016 16:33:24 GMT

You want to see 50,000 because it is Device = NULL? Will that be true for other countries also? If it is, then you can try this:

Sum({<Device = {'NULL'}>} [Total Unique Users])

Re: Filtering on Pre Calculated Data

Colin-Albert — Tue, 17 May 2016 16:33:58 GMT

Just load the rows where Device > '' (or Device <> 'NULL') to exclude the Country Total row, then your data will sum correctly in Qlik.

The other option is to load the raw data and let Qlik calculate the sum. This may prove more flexible. 50,000 rows is not big for Qlik systems.

Re: Filtering on Pre Calculated Data

Tue, 17 May 2016 17:10:26 GMT

Thanks for the responses. The actual table I have is a lot larger and with more dimensions than the illustrated (simplified) version above. Attached is a screenshot of the table I'm working on.

The QV dashboard needs to be built such that:

When Country "AD" is selected (and nothing else) it returns 1,598 users
When Country "AD" and Product="Prepay" is selected it returns 71 Users
When Country "AD" and Product="Prepay" and Date="10/05/2016" is selected it returns 24 Users
and so on...

I agree that only the raw user level data will provide the most flexibility however this data is very large (100s of millions of rows) and will grow over time even larger. The example here is for one of our smallest countries (Andorra!) so you get the picture.

I have tried to use Qlik Expressions with the below using functions like GetCurrentSelection or GetSelectedCount for each field, but haven't managed to come up with anything neat yet. Is there anything I can do with the Group ID column below ?

Thanks

Re: Filtering on Pre Calculated Data

sunny_talwar — Tue, 17 May 2016 23:21:49 GMT

Try this:

=Only({<Row = {"=If(GetSelectedCount(Device) = 1, Len(Trim(Device)) > 0, Len(Trim(Device)) = 0) and

If(GetSelectedCount(Product) = 1, Len(Trim(Product)) > 0, Len(Trim(Product)) = 0) and

If(GetSelectedCount(Date) = 1, Len(Trim(Date)) > 0, Len(Trim(Date)) = 0)"}>}TOTAL_UNIQUE_USER)

You might need to add another If statement in the search string for Country as well.

I am attaching the sample for you to look at.

Best,

Sunny

NOTE: Instead of providing an image, it would be best if you can provide the sample in an Excel file.

QlikCommunity Tip: How to get answers to your post?‌

Re: Filtering on Pre Calculated Data

swuehl — Tue, 17 May 2016 23:59:51 GMT

Thank you Sunny for the sample (I absolutely agree that it's really helpful to post table files or INLINE tables as text to show some input records, and that it's not much fun recreating sample records from screenshots)!

I think the expression can be slightly simplified to

=FirstSortedValue(TOTAL_UNIQUE_USER, -GROUP_ID)

Re: Filtering on Pre Calculated Data

sunny_talwar — Wed, 18 May 2016 00:03:12 GMT

I had a feeling that your much simpler expression is on its way

Re: Filtering on Pre Calculated Data

Wed, 18 May 2016 09:10:38 GMT

Thanks both for your responses to this.

I apologise for not attaching the data, this was my first time posting in these communities and I could only see an attachment for images in the client I was using yesterday. I've now attached the data file including the full data for all countries, so you'll find there's an extra dimension added to the cube.

I sadly can't open the .qvw files as I'm using a personal edition - is there a way around this?

I should also mention that I am building this in QlikSense but I hope that doesn't affect the formulae?

Thanks again

Re: Filtering on Pre Calculated Data

Anonymous — Wed, 18 May 2016 10:09:14 GMT

Hi,

Try this

Max(Aggr(Sum(TOTAL_UNIQUE_USER),Device,Country))

Kind regards

Re: Filtering on Pre Calculated Data

sunny_talwar — Wed, 18 May 2016 12:14:20 GMT

Stefan's expression should still work in QlikSense:

=FirstSortedValue(TOTAL_UNIQUE_USERS, -GROUP_ID)

Re: Filtering on Pre Calculated Data

sunny_talwar — Wed, 18 May 2016 12:18:18 GMT

Attaching the sample for you to review

Re: Filtering on Pre Calculated Data

swuehl — Wed, 18 May 2016 12:19:55 GMT

edit: Sunny is always faster in creating sample apps...

But his expression should also work in Sense

Re: Filtering on Pre Calculated Data

Wed, 18 May 2016 12:39:49 GMT

Jones, as per my understanding your source table itself have rolling totals for each dimension combinations. In your requirement, I guess your looking only for Total value for the Country.

So while loading the data, add another flag field to identify the row belongs to which level of dimension its belongs too and use the flag field in your set analysis.

Its easy for your DWH team add the another flag field to table or you can Identify based on the null values on your dimension fields.

Re: Filtering on Pre Calculated Data

Wed, 18 May 2016 12:50:50 GMT

Jones, you already have GroupID filed to identify the data level belongs to which dimensions. GroupID 7 belongs to Country so you can simply use it.

Expression:

Sum({<{GroupID={7}}>}Total_unique_Users)

Re: Filtering on Pre Calculated Data

Wed, 18 May 2016 13:09:24 GMT

Thanks for the responses,

The issue I have now is what I do if a user wants to select more than one category per field (e.g. selecting both "Android" and "iOS" should return the overall total, similarly for selecting all dates).

Looks like a redesign of the underlying structure so it's a new discussion, unless someone has any initial thoughts?

My thoughts are that the difficulty would lie in when users select part of the categories, e.g. select two dates. The existing data table will not contain the unique count of users across both these dates. This is why using the user-level raw data is perhaps unavoidable

Re: Filtering on Pre Calculated Data

swuehl — Wed, 18 May 2016 15:34:15 GMT

Right.

(I mean, please create a new thread if you have new questions / requests. And yes, I think you would need to go down to higher granularity of your raw data if you want to be able to get the DISTINCT IDs of your raw data.)