Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

AGGR and DISTINCT = Sllllow!

Hi People,

i was hoping if people can help me with an alternative...

i managed to use a formula for a few months, but now the application is so slow, my IT department are talking about removing it until i can find a faster alternative.

you may have seen some previous posts of mine where the raw basics of my application is to track the amount of contacts to a certain customers. The user can then drill into this detail a lot more if they need to.

the data model is basic and consists of a FACT table which has 17Million records in a 12 month period.

so 10 clients could of had 20 hits of activity, by 20 different products and so on...

you end up with a table like this

Capture.PNG.png

to get here i use the  calculated dimension of =aggr(count(person),person) and an expression of count(distinct person)

but i think the Distinct and Aggr are really slow and cpu heavy.

is there any other way i can calulate this, bear in mind it needs to be dynamic as static columns in the data model will not work for obvious reason.

7 Replies
ashfaq_haseeb
Champion III
Champion III

Hi,

You can try by taking calculation to backend.

Regards

ASHFAQ

Not applicable
Author

What does back-end mean?

ashfaq_haseeb
Champion III
Champion III

In the script.

Regards

ASHFAQ

Not applicable
Author

I'm not sure (or my experience doesn't allow) its possible.

if a user clicks on the number 14 for instance then the dashboard will show all the customers that have been contacted 14 time, in this case 180.

there can be duplicate customers contacted by multiple channcel, email, sms, telephone etc, so the number will not work being static

Not applicable
Author

Can you attach a sample file if possible to see if the calculations can be done on the back-end. Its seems like it can be done but tough to say without looking at the Data-Model.

lft
Employee
Employee

Hi Mat,

your dimension and expressions are correct. There is no other way to do what you want to do in a faster fashion in the straight table in QlikView GUI.

as mentioned above you could pre-calculate this aggregation in the script, decreasing the CPU load at runtime when the user makes selections.

You would end up with a table at the granularity of "Number of contacts" (1, 2, ..., N), so a table with N rows.

A big warning though : if you do that, you will loose the ability to make selections in other fields. This table will be a data island and won't be linked to the rest of the model, drastically reducing your analysis capabilities.

You could always make this pre-calculation at "Product" & "Number of contacts" level in order to be able to filter on the products, but then the real fun begins since your KPI ("Number of customers") is not additive (count distinct). It'll be the same with other fields.

In other words, if you want to keep the analysis capabilities, you need the stay at the detailed level and keep it dynamic (obvious you said it).

That's what you have done, and there you go, back to the beginning, you have performance issues.

One thing you could do then is increase the platform CPU / RAM / Memory bandwidth. What are the specs of your server ? Is it virtualised ? Have you run a performance benchmark on the platform when using this application ?

One other thing you could do is to reduce the depth of data. Make one application with only one month of data, the other one with the whole history, and document chaining between both. If the users want speed they'll only access to the last month of data. If they want depth, then they surely can wait a little longer.

With both choices I strongly suggest you to get close to your local Qlik account manager. He will provide you access to our consultants that can help you with this kind of performance questions.

Loic

Not applicable
Author

At least its stop me thinking i've missed something so really really helpful so thank you....

maybe sub setting the data will be helpful -  i will run some tests