Calculation between dates

[ptl14495]

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

[sunny_talwar]

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]

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]

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]

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]

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]

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]

Check here:

Creating A Master Calendar