Line Chart - Show Null Values as Zero in a Month Dimension

Fri, 25 Jul 2014 03:46:04 GMT

Hello!

I have a line chart like the following:

X axis: Month dimension
Y axis: Territory for every Month

Each line represents a specific Territory with a value for the monthsThe thing is that not all the months have a value. Some of them are null.What I want is to show all months, whether they have a value or not, in the last case, I'd like to have a 0 for the value.So that I'd see all the months and the corresponding value for every territory.For that line chart I'm using:

MONTH as a Dimension
TERRITORY as a Dimension
Expression

COUNT ({$<FLAG_Anomaly={"With Anomaly"}>} DISTINCT ACCT_ID_COUNT)

COUNT ({$<FLAG_ANOMALY={"With Anomaly","No Anomaly"}>} DISTINCT ACCT_ID_COUNT)

But there are some months like January and February where the value is null...

Do you know how could I do that, to turn the value I get from the expression to 0?

Thank you!

Re: Line Chart - Show Null Values as Zero in a Month Dimension

Fri, 25 Jul 2014 04:48:21 GMT

Hi Mart

Try to use a Master Calender which creates all the dates from the beginning to the end.

Something like this in script:

Temp:
LOAD
min(%DateKey) as MinDatum,
max(%DateKey) as MaxDatum
resident LogonEvent;

LET vMinDatum = Peek('MinDatum', 0, 'Temp');
LET vMaxDatum = Peek('MaxDatum', 0, 'Temp');
DROP Table Temp;

TempKalender:
LOAD
date($(vMinDatum) + RowNo()-1) as TempDate
// timestamp($(vMinDatum) + (RecNo()/12/24) + (IterNo()-1)) as Minutegeneriert -> generiert alle 5 Minutenwerte!
AutoGenerate($(vMaxDatum) -$(vMinDatum)+1);

MasterCalendar:
LOAD
num(TempDate) as StartTimeCalendar,
Date(TempDate) as MasterCalendarDate,
Date(TempDate) as %DateKey,
Year(TempDate) as Jahr,
'Q'& ceil(month(TempDate)/3) as Quartal,
Month(TempDate) as Monat,
date(monthstart(TempDate), 'MMM-YYYY') as Kalendermonat,
WeekYear(TempDate) as Kalenderwoche,
WeekDay(TempDate) as Wochentag,
Year2Date(TempDate) as YTD,
Year2Date(TempDate, -1) as LYTD,
Day(TempDate) as Tag

resident TempKalender;
DROP Table TempKalender;

Please never mind my Fielad Names as i have just copy/paste from one of my applications...

%DateKey must be your DateField of your data.

Kind regards,

Anthony

topic Re: Line Chart - Show Null Values as Zero in a Month Dimension in QlikView

Line Chart - Show Null Values as Zero in a Month Dimension

Re: Line Chart - Show Null Values as Zero in a Month Dimension