topic Re: Adding cumulative field - Degrading dashboard performance in QlikView

Adding cumulative field - Degrading dashboard performance

bharani8 — Wed, 25 Nov 2020 16:16:04 GMT

Hi Experts - Any idea what is the work around for this? While adding the cumulative calculation field in an straight table, my dashboard performance is degrading (takes 5 to 10 Mins to refresh). Plz Help.. Let me know if any other info is required?

Thanks!

Bharani

Re: Adding cumulative field - Degrading dashboard performance

Anil_Babu_Samineni — Mon, 04 Sep 2017 07:47:54 GMT

Can you cross check the RAM for that application in your Task Manager? And indicate us whether how many records are there? And explain the formula you used ??

Re: Adding cumulative field - Degrading dashboard performance

bharani8 — Mon, 04 Sep 2017 08:11:03 GMT

Hi i see as below in mu Task manager... I would have loaded around 15 Lakhs rows.. and the formula for calculating cumulative i used was

rangesum(above(total sum({<AGED_DAYS_SUMMARY_ID -= {'[6] NO RECEIPT'}>}USD_INV_DOLLARS)/1000000, 0, rowno(Total)))

and also tried below formula as well

above(sum({<AGED_DAYS_SUMMARY_ID -= {'[6] NO RECEIPT'}>}USD_INV_DOLLARS)/1000000)+sum({<AGED_DAYS_SUMMARY_ID -= {'[6] NO RECEIPT'}>}USD_INV_DOLLARS)/1000000

Re: Adding cumulative field - Degrading dashboard performance

Anil_Babu_Samineni — Mon, 04 Sep 2017 08:13:32 GMT

Only 15 L, Then it won't problem. Could be problem with RAM size only. What was RAM ??

Re: Adding cumulative field - Degrading dashboard performance

bharani8 — Mon, 04 Sep 2017 08:15:25 GMT

8GB RAM..

Re: Adding cumulative field - Degrading dashboard performance

Anil_Babu_Samineni — Mon, 04 Sep 2017 08:22:15 GMT

I even wonder - Why 5-10 Minutes occupied to fetch data? Does this getting stuck or some thing new? Because my specification are very lower than your and i am maintaining some around 2 M - 4 M Rows in my report. May be marcus_sommer has some insights

For a While, Could you try this for me. I don't recommend to use -= but this may useful for you

rangesum(above(total sum({<AGED_DAYS_SUMMARY_ID = E({<AGED_DAYS_SUMMARY_ID = {'[6] NO RECEIPT'}>}AGED_DAYS_SUMMARY_ID)>}USD_INV_DOLLARS)/1000000, 0, rowno(Total)))

Re: Adding cumulative field - Degrading dashboard performance

bharani8 — Mon, 04 Sep 2017 08:25:47 GMT

Ok Sure .. i will give a try on this and keep you updated..

It is not getting stuck.. it is taking time to refresh.. with that Green Sleeping line...

Re: Adding cumulative field - Degrading dashboard performance

qliksus — Mon, 04 Sep 2017 08:35:59 GMT

So does using a simple expression like this too take the same time?

rangesum(above(total sum(USD_INV_DOLLARS)/1000000, 0, rowno(Total)))

Also as you are using a straight table why not use the in-built feature for accumulation using the below expression

sum({<AGED_DAYS_SUMMARY_ID = E({<AGED_DAYS_SUMMARY_ID = {'[6] NO RECEIPT'}>}AGED_DAYS_SUMMARY_ID)>}USD_INV_DOLLARS)/1000000

Re: Adding cumulative field - Degrading dashboard performance

bharani8 — Mon, 04 Sep 2017 09:04:01 GMT

Even this is taking same amount of time to refresh

Re: Adding cumulative field - Degrading dashboard performance

bharani8 — Mon, 04 Sep 2017 09:05:11 GMT

Full Accumulation is not working when we have more than 1 Dimension.. Not sure Why..

Re: Adding cumulative field - Degrading dashboard performance

qliksus — Mon, 04 Sep 2017 09:46:51 GMT

How many dimensions you have in the table? . Also just for testing can you reduce the data to around 5L and use the same expression and check the result it could be either because of RAM or a poor data model

Re: Adding cumulative field - Degrading dashboard performance

marcus_sommer — Mon, 04 Sep 2017 09:54:17 GMT

I assume that you want to calculate it on many rows within the straight-table and this will take some time because each single row will need a separate calculation on the virtual table within the background: Logical Inference and Aggregations.

Personally I don't remember any special performance-issues with this kind of object and calculation but I remember other discussions about poor performance on it. One way to improve the performance could be to limit your table-rows by removing/replacing some very granular dimensions and/or conditions within the object/dimensions to display max. n number of rows.

A more general approach would be to look if there are possible optimizations within the datamodel - snowflake models or link-table approaches will have a lower gui-performance than a star-scheme or a big flat-table.

- Marcus