# App Development

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

Announcements
QlikWorld Online 2021, May 10-12: Our Free, Virtual, Global Event REGISTER TODAY
cancel
Showing results for
Search instead for
Did you mean:
Contributor III

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

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

MVP

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

Contributor III
Author

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

Contributor III
Author

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

MVP

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)

Contributor III
Author

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

MVP