topic Re: Re: Chart with counts on the regular dates in QlikView

Chart with counts on the regular dates

Wed, 05 Mar 2014 12:30:56 GMT

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!

Re: Chart with counts on the regular dates

MK_QSL — Wed, 05 Mar 2014 12:41:38 GMT

Can you upload your sample data file?

Re: Chart with counts on the regular dates

Wed, 05 Mar 2014 12:58:14 GMT

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.

Re: Chart with counts on the regular dates

MK_QSL — Wed, 05 Mar 2014 13:28:22 GMT

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

Re: Chart with counts on the regular dates

Wed, 05 Mar 2014 13:45:54 GMT

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

Re: Re: Chart with counts on the regular dates

Wed, 05 Mar 2014 13:59:23 GMT

Here's sample file with part of data

Re: Chart with counts on the regular dates

Colin-Albert — Wed, 05 Mar 2014 14:00:10 GMT

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

Re: Chart with counts on the regular dates

MK_QSL — Wed, 05 Mar 2014 15:03:03 GMT

Please check enclosed file.

Hope this helps...

Re: Chart with counts on the regular dates

MK_QSL — Wed, 05 Mar 2014 15:20:22 GMT

Check this one also...

Re: Re: Chart with counts on the regular dates

Tue, 11 Mar 2014 12:57:39 GMT

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.

Re: Re: Chart with counts on the regular dates

Tue, 11 Mar 2014 13:46:45 GMT

In other words the Doc, you pointed at, suggests that I would build an extra table with intersections of CIDs and all the dates when these CIDs are valid, am I rigth? And maybe I can build the table with dates corresponding to the beginnings of the months to reduce the data load. But building a new table just to make some counts over it... Damn, didn't expect that...

Re: Re: Chart with counts on the regular dates

Wed, 12 Mar 2014 14:39:42 GMT

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