topic Re: Alt function for filling missing data in diagramm / monthly in App Development

Alt function for filling missing data in diagramm / monthly

troggy — Mon, 22 Oct 2018 07:50:25 GMT

Hi,

I am trying to setup a periodic diagramm with drill down to details. When there is no data for a selection, e.g. product xz has no notification in April, the diagramm will not show the month. I want to show 0 here.

I tried it with alt function, but this is not working:

alt(count( {<"KPI"={'Notification'}, DATETYPE={'Creation'}>} distinct [Notification]),0)

Dimensions is set as follows with an auto calendar

Filling 0 values in load script is not a solution as there is a lot of data for every possible period/product/region combination.

Any idea on formular?

Re: Alt function for filling missing data in diagramm / monthly

marcus_sommer — Tue, 23 Oct 2018 14:09:15 GMT

I'm not sure if it's solvable with Sense but in View it's often possible by using the NULL options in tab dimensions and presentation in combination with an expression like:

rangesum(count( {<"KPI"={'Notification'}, DATETYPE={'Creation'}>} distinct [Notification]),0.0000000001)

With an appropriate formatting it looked like 0. Further I suggest to replace the autocalendar with a real calendar, see: How to use - Master-Calendar and Date-Values to ensure that you have at least dates for the not existing product data.

- Marcus

Re: Alt function for filling missing data in diagramm / monthly

troggy — Wed, 24 Oct 2018 09:29:44 GMT

Thanks Markus for the Input.

Workaround with 0.00001 is working, though I'd rather prefer to set this up correctly. So filling up empty for main dates might be really better idea.

With an appropriate formatting it looked like 0. Further I suggest to replace the autocalendar with a real calendar, see: How to use - Master-Calendar and Date-Values to ensure that you have at least dates for the not existing product data.

Regarding calendar, I stated autocalendar wrongly. Indeed I am using a master calender as table CALENDAR. But still struggling on how filling up empty entries could work.

Date information is excluded from my fact table to a separate help table META_DATES with columns KEY, DATETYPE and DATEVALUE.

Calender table creates an entry for every day in my analysis period:

FOR i = $(vMinDate) TO $(vMaxDate)

CALENDAR:

LOAD '$(i)' AS DATEVALUE

,date('$(i)','DD.MM.YYYY') AS [Date Format]

...

AUTOGENERATE(1)

So I have for every existing notification date in help table an numeric value in CN_DATEVALUE which is reffering to my calendar. If no value in table META_DATES I won't get a hit in my graph's set analysis. (Notification is not existing for this criteria in count( {<"KPI"={'Notification'}, DATETYPE={'Creation'}>} distinct [Notification] )

Would It be more reasonable to count somehow the days in calendar table with existing notification?

but following is also not filling empties...

alt(count( {<"KPI"={'Notification'}, DATETYPE={'Creation date'}>} distinct DATEVALUE),0)

Re: Alt function for filling missing data in diagramm / monthly

marcus_sommer — Fri, 26 Oct 2018 05:48:15 GMT

It's depending on your datamodel and how data should be displayed if such a workaround like above mentioned fullfilled all requirements. If not then is it often necessary to create the missing data, see: Generating Missing Data In QlikView.

- Marcus

Re: Alt function for filling missing data in diagramm / monthly

troggy — Mon, 11 Feb 2019 12:46:26 GMT

If someone has same issues:

We solved it now as follows, as we do not want to create loads of "empty" data.

Add a count of the unique month dimension *0

=count( {<"KPI"={'Notification'}, Datetypye={'Creation date'}>} distinct [Notification])

+(count( distinct "Unique Month")*0)