Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a table with structure like the follows:
table:
load * inline
[
CID, Type, BegDate, EndDate
1,1,01.01.2010,31.01.2014
2,2,01.01.2010,25.05.2010
3,2,18.03.2011,24.06.2013
4,2,13.02.2012,14.03.2013
5,1,11.03.2011,15.02.2014
6,1,13.04.2013,16.11.2013
]
It contains the data about contracts. BegDate can't be null, EndDate can (that means, the contract is active). I need a line chart displaying the number of active contracts of every type on the 1st day of every month beginning from, say, 01.01.2010 and till Today(). If during the month there is no new contracts and no ending contracts it still has to be displayed, it's values are the same as the previous month. As far as I know, I'll have to autogenerate a table with desired dates (the 1st days of months). And what should I do next? Try to populate it with count values during the load process? If yes, how? Or use it as dimension to draw a chart and use set analysis to apply count conditions? Thanks!
Solved for me. I've built an isolated calendar, have taken it's MonthStart(Temp_Date) as MStart and main table's type as dimensions and Count(Distinct if(([BegDate]<=MStart)and(IsNull([EndDate])or([EndDate]>MStart)),CID)) as the expression. It seems to count OK.
The main idea was taken from the author's comments to the topic here http://community.qlik.com/blogs/qlikviewdesignblog/2012/10/16/the-master-calendar
Can you upload your sample data file?
I can't upload real task file, because it contains personal data, but you may use the inline table and provide a solution based on it. I think I'll face no difficulties transfering the solution to real database.
Can you provide some sample data without attaching the actual file.
The data itself is a kinda personal. Ok, I'll try to refine it to make the publishing possible.
Here's sample file with part of data
This document by Henric Cronström has the answer you are looking for.
See the example on page 9. Creating one record per day that a contract is valid
Please check enclosed file.
Hope this helps...
Check this one also...
This one does not take into account the amount of the contracts that came to an end. And it doesn't have type field as a dimension. If I add it, I get unexpected results. I'll try to modify your solution so it would meet my requirements.