topic Showing NULL revenues in QlikView

Showing NULL revenues

Wed, 20 Jan 2010 15:51:21 GMT

Hello,

I want to display a table with all the days of a month (1-28,29,30, or 31) and the revenue made on that day.

So, the field 'Day' is my dimension, '=sum(rev)' is my expression.
Fine.

But my data is sparse, i.e. not on all days I can show revenue, e.g. in the middle of the current month.
So, QV shows only the days where I made revenue.

How can I convince QV to show all days, and provide a NULL on days w/out revenue?

Thank you,
Thilo

Showing NULL revenues

Wed, 20 Jan 2010 15:53:37 GMT

Thilo

Do you have every day stored in a table, with a link to sales where relevant?

Showing NULL revenues

Wed, 20 Jan 2010 17:03:41 GMT

Nigel,

(simplified) I have one table Sales with 2 fields salesdate and rev.
I simply load that table.

After that, I do

CTmp:
load distinct salesdate as dtTmp
resident Sales;

Calendar:
load
dtTmp as salesdate,
Day(dtTmp) as Day,
Month(dtTmp) as Month,

.... other calender stuff ...

resident CTmp;
drop table CTmp;

So I have 2 tables (Sales, Calendar) connected via field salesdate.

Can I interpret your question that I have to ensure that Calendar includes ALL dates, not just the existing ...

Showing NULL revenues

Wed, 20 Jan 2010 17:15:57 GMT

Thilo

I think you would need to create a single table that has all dates, probably using a cross join to ensure you have it all in a single table. I have tried in the past to achieve this without everything in the same table but it is impossible as far as I am aware, QlikView is designed to show you data that is there, rather than data that is not!

Sorry, but hope it helps.

Showing NULL revenues

Wed, 20 Jan 2010 17:38:57 GMT

Nigel,


Nigel West wrote:QlikView is designed to show you data that is there, rather than data that is not!

True, true ...
Unfortunately, due to other aspects of the app (not just sales, also costs, budgets, in various versions, 3 year history) I would like to avoid the cross-join solution.

Anyways, thanks again for your feedback ... if I find a solution that avoids 'filling the gaps' I will post it here.

Thanks,
Thilo

Showing NULL revenues

Wed, 20 Jan 2010 19:18:09 GMT

Nigel,

thanks for sending me into the right direction.

I (think I) solved the problem by filling the gaps, not in the Sales table, but in the Calendar table.

I calculated the min and max date (using min/ max functions), and then generated the calendar based on load ... autogenerate.

I used MonthStart/ MonthEnd functions to ensure complete months.
It works (at least for me and my specific problem).

Thanks again for helping me think! 😉
Thilo