topic How to Count a Dimension with no Data in QlikView

How to Count a Dimension with no Data

cranium144 — Thu, 15 Oct 2015 14:09:05 GMT

I'm looking to calculate the quantity sold per year, which is fairly easy. My statement is this:

=num(sum([Invoice Qty]) / Count(DISTINCT InvoiceMonthYear),'#,##0')

However, if one of the InvoiceMonthYear values has no data in it (grayed out), how do I include those non-existent months?

For example, if I have selected 6 months, with a total quantity of 60,000, I want to display the result of 10,000 per month. But only 2 of those months have data, so I'm getting 30,000.

I'm using MonthYear because the time frame may span more than a single year.

Thanks!

Rick

Re: How to Count a Dimension with no Data

cranium144 — Thu, 15 Oct 2015 14:12:07 GMT

Pardon, I typed the first line incorrectly. "quantity sold per month" is what I intended.

Re: How to Count a Dimension with no Data

Oleg_Troyansky — Thu, 15 Oct 2015 14:18:35 GMT

Hi Rick,

there is a number of ways to solve this issue.

1. Based on your description, it sounds like you don't have a Master Calendar in your dataset. This is why only 2 months "have data" when you selected 6 months. If you create a Master Calendar and list ALL the dates (months) in your date range, then you'd always have all the months in the range available, whether they have sales or not.

2. Without a master calendar, you might solve the problem by calculating the difference between the Max month and the Min month in the available range. However, this would only work if you are sure that the first selected month and the last selected month have data. Otherwise, you still won't get the desired result. So, back to the master calendar.

3. Depending on your data, you may get by with the functions like GetSelectedCount or GetPossibleCount. However, based on your description of the problem, you'll probably face the same issue - your selections are made on the fields Year and Month (I'm guessing) while your possible values are affected by the transactional data. So, once again - Master Calendar is your best friend here.

cheers,

Oleg Troyansky

QlikView Your Business: An expert guide to Business Discovery with QlikView and Qlik Sense

Re: How to Count a Dimension with no Data

Qrishna — Thu, 15 Oct 2015 14:22:39 GMT

this way?>

Re: How to Count a Dimension with no Data

cranium144 — Thu, 15 Oct 2015 14:43:37 GMT

I like this look.

So, how did you manipulate the data to have a zero?

Re: How to Count a Dimension with no Data

cranium144 — Thu, 15 Oct 2015 14:45:33 GMT

Understood about the Master Calendar. However, we stayed away from the master calendar because we have some "open" orders out to the year 2049. I don't want to slam the year selection full of blank years and months.

We have one big facts table with invoices and open orders so I can have 1 field for both invoices and open orders (and customer names, ship to names, etc.). I just have a hidden selection depending on the tab.

Re: How to Count a Dimension with no Data

Qrishna — Thu, 15 Oct 2015 14:47:08 GMT

See attached.

Re: How to Count a Dimension with no Data

Qrishna — Fri, 16 Oct 2015 14:59:05 GMT

Any Luck? Did my solution work for you?

Re: How to Count a Dimension with no Data

cranium144 — Fri, 16 Oct 2015 15:21:15 GMT

For now, I've decided to not change anything. Adding "zero" values would get rid of the green/white/gray model that Qlik is founded on. But I will mark it as a possible answer.