Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
Hi Gysbert,
I would like to know what is the difference between Missing Symbol & NULL Symbol in QV tables ?
Missing vs NULL ?
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
Hi Gysbert,
Thanks for the reply. However it does not display the previous week's result. I still get '-'. Any thoughts?