Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Chart with counts on the regular dates

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!

1 Solution

Accepted Solutions
Not applicable
Author

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

View solution in original post

11 Replies
MK_QSL
MVP
MVP

Can you upload your sample data file?

Not applicable
Author

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.

MK_QSL
MVP
MVP

Can you provide some sample data without attaching the actual file.

Not applicable
Author

The data itself is a kinda personal. Ok, I'll try to refine it to make the publishing possible.

Not applicable
Author

Here's sample file with part of data

Colin-Albert

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

http://community.qlik.com/docs/DOC-3786

MK_QSL
MVP
MVP

Please check enclosed file.

Hope this helps...

MK_QSL
MVP
MVP

Check this one also...

Not applicable
Author

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.