topic Re: Calculate default for missing '-' value in pivot tables in QlikView

Calculate default for missing '-' value in pivot tables

Sun, 06 Oct 2013 14:56:46 GMT

Hi all,

I'm having difficulty in calculating a default value for a missing value '-' in my pivot table.

Basically I have 2 dimensions week (WW) and day (dd). Values (volume)are spread out over 24 hour period.

Objectively, if there aren't values for a certain period, I should be able to replace with a default value or equivalent value from a previous week within the exact hour frame.

Greatly appreciate your help, thanks.

if (fabs(Sum([VolumeCT]) <0) ,

sum([VolumeCT])+RangeSum (above(Sum([VolumeCT]),+1,Count(dd))),

Sum (VolumeCT)

)

Thanks in advance

Rui

Re: Calculate default for missing '-' value in pivot tables

Gysbert_Wassenaar — Sun, 06 Oct 2013 17:56:10 GMT

This will always return false since the absolute of any number is always equal or larger than zero: fabs(Sum([VolumeCT]) <0

You can try something like if(sum(VolumeCT),sum(VolumeCT),above(sum(VolumeCT))). That should display the previous weeks value, but that amount will not be counted in the subtotals. If you need that you'll have to fill in the missing values in the script.

Re: Calculate default for missing '-' value in pivot tables

Mon, 07 Oct 2013 04:24:18 GMT

Hi Gysbert,

I would like to know what is the difference between Missing Symbol & NULL Symbol in QV tables ?

Missing vs NULL ?

Re: Calculate default for missing '-' value in pivot tables

Gysbert_Wassenaar — Mon, 07 Oct 2013 08:05:22 GMT

Which symbol is used seems to depend on the Supress Zero-Values setting. If that setting is enabled the Missing symbol is used for missing and null values. If it is disabled then the Null symbol is used.

See this document for the difference between nulls and missing values: NULL handling in QlikView

Re: Calculate default for missing '-' value in pivot tables

Mon, 07 Oct 2013 19:23:19 GMT

Hi Gysbert,

Thanks for the reply. However it does not display the previous week's result. I still get '-'. Any thoughts?