topic Re: Calculation between dates in App Development

Calculation between dates

ptl14495 — Tue, 25 Jul 2017 10:27:30 GMT

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 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

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

Re: Calculation between dates

sunny_talwar — Wed, 26 Jul 2017 17:21:07 GMT

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

Re: Calculation between dates

sunny_talwar — Wed, 26 Jul 2017 17:32:04 GMT

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])

Re: Calculation between dates

ptl14495 — Fri, 28 Jul 2017 08:49:08 GMT

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

Re: Calculation between dates

ptl14495 — Fri, 28 Jul 2017 14:07:47 GMT

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

Re: Calculation between dates

sunny_talwar — Fri, 28 Jul 2017 15:17:55 GMT

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)

Re: Calculation between dates

ptl14495 — Fri, 28 Jul 2017 15:48:17 GMT

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

Re: Calculation between dates

sunny_talwar — Fri, 28 Jul 2017 16:22:58 GMT

Check here:

Creating A Master Calendar