Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
ptl14495
Not applicable

Calculation between dates

Hello.

I am trying to calculate a value between dates.

The information I have is as follows, No. of Contract, Beginning of Contract, End of Contract.

Objective, know how many contracts are active in a given month, or day.

No. of ContractBeginning of ContractEnd of Contract
12301/04/201731/12/2017
45601/01/201730/04/2017
78931/12/201731/12/2018

In the table above I would have;

April month; 2 active contracts (Contract 123 and 456)

01/05/2017; 1 Contract assets (Contract 123)

01/01/2018; 1 Active contract (Contract 789)

Can anyone help me out with the solution

Thank you

Fernando Rodrigues

7 Replies
sunny_talwar
Not applicable

Re: Calculation between dates

Use IntervalMatch to connect your master calendar with the fact table and then use Month or MonthYear as dimension.... and count(distinct contracts) as your expression

sunny_talwar
Not applicable

Re: Calculation between dates

Another method....

Script

Table:

LOAD *,

Date(AddMonths([Beginning of Contract], IterNo() - 1)) as Date,

Month(AddMonths([Beginning of Contract], IterNo() - 1)) as Month,

Year(AddMonths([Beginning of Contract], IterNo() - 1)) as Year,

MonthName(AddMonths([Beginning of Contract], IterNo() - 1)) as MonthYear

While AddMonths([Beginning of Contract], IterNo() - 1) <= [End of Contract];

LOAD * INLINE [

    No. of Contract, Beginning of Contract, End of Contract

    123, 01/04/2017, 31/12/2017

    456, 01/01/2017, 30/04/2017

    789, 31/12/2017, 31/12/2018

];

Dimension

MonthYear

Expression

Count(DISTINCT [No. of Contract])

ptl14495
Not applicable

Re: Calculation between dates

Hello Sunny,

I was trying to do this in the app itself, with an expression.

I have seen your solutions and i think they both include script editing (something i am not ready for yet )

do you think this solution could work as well?

Count( {$<Year = {“>2014/04/01<2014/04/30”} >}, [No. of Contracts] )

I am not able to teste since Qlik is currently offline.

thank you

Fernando Rodrigues

ptl14495
Not applicable

Re: Calculation between dates

Hi Sunny,

My goal is to count how many contracts are "live" in a specific Year, Month Day.

for that, i must set two variables.

For example, I want to know how many contracts are "live" in 01/02/2017.

For that i must say that,

count all contracts that meet these two criterias,

Beginning of the Contract >=01/02/2017

End of Contract >=01/02/2017

thank you

Kind Regards

Fernando Rodrigues

sunny_talwar
Not applicable

Re: Calculation between dates

Since you have two dates, you need a third date which may be from your master calendar? Do you have a master calendar setup? You might be able to use a front end logic to do this... but I would highly recommend against it. The reason is very simple, the front end expression will be complex and will slow down your dashboard. If that is not an issue, we can work on getting it done on front end as well (all you need is a calendar script or something around an independend MonthYear field)

ptl14495
Not applicable

Re: Calculation between dates

Hi Sunny,

I haven't created a Master Calendar yet and actually i don't know how.

How can/should i do it?

thank you

Fernando Rodrigues

sunny_talwar
Not applicable

Re: Calculation between dates