Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
Need help to replace NULL Values to ' ' (Blank) in Pivot table in Qlik Sense.
Below is the script and data
Table:
LOAD *,
Month as Month1,
Year(Month) as Year;
LOAD *,
Volume/((Month(End_Date)+Year(End_Date)*12) - (Month(Start_Date)+Year(Start_Date)*12)+1) as VolumePerMonth,
Date(MonthStart(Start_Date, IterNo() - 1), 'YYYYMM') as Month
While MonthStart(Start_Date, IterNo() - 1) <= End_Date;
LOAD * INLINE [
Project_ID, Start_Date, End_Date, Volume
1, 01/01/2019, 12/31/2019, 120000
2, 03/01/2019, 06/15/2019, 48000
3, 06/01/2019, 10/31/2019, 200000
4, 01/01/2019, 12/31/2019, 120000
5, 03/01/2019, 06/15/2019, 48000
6, 06/01/2019, 10/31/2019, 200000
7, 01/01/2019, 12/31/2019, 120000
8, 03/01/2019, 06/15/2019, 48000
9, 06/01/2019, 10/31/2019, 200000
10, 01/01/2019, 12/31/2019, 120000
11, 03/01/2019, 06/15/2019, 48000
12, 06/01/2019, 10/31/2019, 200000
]
;
Dimension used in Pivot :Project_Id, Month
Expression used : SUM(Volume)
Tried below options but didn't worked.
IF(ISNULL(SUM(Volume)),' ',SUM(Volume)), ALT(SUM(Volume), ' '),Replace (), IF(LEN(SUM(Volume))=0,'',SUM(Volume))
at script level
NullAsValue Project_ID,Month;
Set NullValue = 'NULL';
Attached is the screen print
Those aren't null values but missing values. The only way solve that is to add extra data to the data you load for all the missing combinations of product and month values.
Those aren't null values but missing values. The only way solve that is to add extra data to the data you load for all the missing combinations of product and month values.
@Gysbert_WassenaarThanks for a response!!
There is no data for these combinations ,is there any other alternative to replace missing values with 0 or ' '.
alt(exp,'')
it does not work:
Have you solved the prolem? Thanks