Alt function for filling missing data in diagramm / monthly

[troggy]

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?

[marcus_sommer]

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

[troggy]

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)

[marcus_sommer]

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

[troggy]

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)