Showing NULL revenues

[#1]

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

[#2]

Thilo

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

[#3]

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 ...

[#4]

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.

[#5]

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

[#6]

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