Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Now Live: Qlik Sense SaaS Simplified Authoring – Analytics Creation for Everyone: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Create snap shot chart from the current data

Hello all,

I have a contract table consisting following columns:

Contract_Id,

Contract_Name,

Contract_startdate,

Contract_enddate,

Contract_status

Based on the start and end date, the contract status will be 'Active', 'Expired' or 'Future'.

I would like to create a chart in which my dimension axes will be time dimension and would like to show how many 'Active' contracts for each time frame.

Please look at the example data below

     

contarct_idcontract_namecontract_startdatecontract_enddatecontract_status
1abc1/1/201212/31/2015Expired
9ijk2/1/20121/31/2016Expired
16pqr3/3/20133/2/2015Expired
5efg4/1/20133/31/2018Active
19stu4/3/20134/2/2014Expired
7ghi5/31/20135/30/2016Active
8hij8/1/20137/31/2018Active
12lmn1/1/201412/31/2015Expired
2bcd3/10/20143/9/2017Active
3cde4/4/20144/3/2016Expired
18rst6/1/20145/31/2015Expired
6fgh7/1/20146/30/2017Active
14nop9/3/20149/2/2017Active
15opq12/21/201412/20/2016Active
13mno3/3/20153/2/2016Expired
17qrs5/19/20155/18/2017Active
11klm6/5/20156/4/2016Active
4def6/1/20165/31/2019Future
10jkl8/1/20167/31/2019Future
20tuv9/2/20169/1/2019Future

Based on this data, I would like to create a bar chart showing how many 'Active' contracts within each time frame i.e. if we show by Month than how many 'Active' contract in that month at that time.

Please see below for the expected result table:

   

Month# of Active Contract in that month at that timeexplanation
Jan 201211
Feb 201222
Mar 201222
Apr 201222
May 201222
Jun 201222
Jul 201222
Aug 201222
Sep 201222
Oct 201222
Nov 201222
Dec 201222
Jan 201322
Feb 201322
Mar 201333
Apr 201355
May 201366
Jun 201366
Jul 201366
Aug 201377
Sep 201377
Oct 201377
Nov 201377
Dec 201377
Jan 201488
Feb 201488
Mar 201499
Apr 20149(10-1) = 9 //-1 for one getting expired in Apr 2014
May 201499
Jun 20141010
Jul 20141111
Aug 20141111
Sep 20141212
Oct 20141212
Nov 20141212
Dec 20141313
Jan 20151313
Feb 20151313
Mar 201513(14-1) = 13 //-1 for one getting expired in Mar 2015
Apr 20151313
May 201513(14-1) = 13 //-1 for one getting expired in May 2015
Jun 20151414
Jul 20151414
Aug 20151414
Sep 20151414
Oct 20151414
Nov 20151414
Dec 201512(14-2) = 12 //-2 for two getting expired in Dec 2015
Jan 201611(12-1) = 11 //-1 for one getting expired in Jan 2016
Feb 20161111
Mar 201610(11-1) = 10 //-1 for one getting expired in Mar 2016
Apr 20169(10-1) = 9 //-1 for one getting expired in Apr 2016
May 20168(9-1) = 8 //-1 for one getting expired in May 2016

Greatly appreciate any help you can provide.

Thank you,

Parth Shah

1 Solution

Accepted Solutions
swuehl
MVP
MVP

Create reference dates for your intervals, then use these reference dates as timeline

Creating Reference Dates for Intervals

(reference dates don't need to be down to date granularity, maybe monthstart would be enough)

View solution in original post

7 Replies
swuehl
MVP
MVP

Create reference dates for your intervals, then use these reference dates as timeline

Creating Reference Dates for Intervals

(reference dates don't need to be down to date granularity, maybe monthstart would be enough)

santiago_respane
Specialist
Specialist

Hi Parth,

First you need some kind of calendar or references as Swuehl mentioned,

where for each date you will have its respective month, quarter, year, etc.

And then in your chart add desired period as dimension and in expression add like this:

=COUNT({<contract_status={'Active'}>} contract_id)

If you want you can add a second expression with Expired ones:

=COUNT({<contract_status={'Expired'}>} contract_id)


And transpose them to compare.

Let me know if this helps.

Kind regards,

Not applicable
Author

Thank you Swuehl. This is amazing.

Regards,

Parth

Not applicable
Author

Hi Santiago,

Thank you for your response. After creating those reference dates I used them as my dimension axes in the chart.

For my expression, I did what you mentioned.

Capture.PNG

By doing that, it only took 'Active' contracts and didnt take any contracts which were 'Active' before but are 'Expired' now. I would like to show those contracts too which were 'Active' before and are 'Expired' now with in their time range.

I guess, if I dont provide this condition in the expression; than I get what I want?

Thank you,

Parth

swuehl
MVP
MVP

If you have created reference dates only for the period they are active, you probably don't need to do anything special, just do a DISTINCT count of your contracts.

Not applicable
Author

Hi Swuehl,

I didnt do anything but by following the approach from the link you posted, it automatically created reference dates for a contract when it was active.

I guess I am all set. Thank you guys. I appreciate your help.

Regards,

Parth

ziadm
Specialist
Specialist

Hi Parth

You have to created Month and Year of your Dates and make these as your dimension and the expression will be count of the active and expired contract

check the attached

if this solve the problem please mark as correct or helpful

Thanks