Use date as reference in set analysis

[#1]

Hi!

I'm trying to compare a contracts start and end date to a date to see how many contracts that are opened at a certain date. I've managed to to this using 'today()' using the following formula:

sum({END_DATE={">=$(=today())"}, START_DATE={"<=$(=today())"}, Period=, Year=, Quarter=, Month= >} Price)

So this one works for today(), but how should I do to replace today() with all dates in a certain span to see how it changes over time? I've tried to use this code:

sum({END_DATE={">=$(Date)"}, START_DATE={"<=$(Date)"}, Period=, Year=, Quarter=, Month= >} Price)

But this date is connected to another Date in the Link Table which creates a problem.

So basically what I want to do is generate all dates for a given span so I can use this date as I use today() i the first formula.

[Gysbert_Wassenaar]

Create two variables vStartDate and vEndDate and use the variable in the expression:

sum({END_DATE={">=$(vStartDate)"}, START_DATE={"<=$(vEndDate)"}, Period=, Year=,Quarter=, Month= >} Price)

You could use calendar objects or a slider object to give the variables values. See this tutorial for an example using calendar objects.

[#3]

Thank you for the tip. However, how should I do to be able to see how the price changes over time? Let's say if I want to create a diagram with every date for a year and see how this changes over the year?

[#4]

No other ideas? Tried to create a Date variable that has no connection to create and start date but this doesn't work at all.

[sushil353]

Hi Kim,

in your diagram/chart you can use {1} in your set analysis.

this will give you results for all date in other words your selection will not affect on your chart.

if you still have issues.. they please post the sample app.

HTh

Sushil

[#6]

Attached is my example file. I've created some random contracts that have start and end date and I want to sum the sales for the contract that are active each day. Hopefully my file explains itself 

[sushil353]

Hello Kim.

Please find the attached modified file.

HTH

Sushil

[#8]

Thanks for this!

However, if I don't choose any date, it still returns the sum of all sales on every date. I want to see how the sum of all active contracts changes over time. So i want to see every date for a whole month and the sum of sales for the active contracts. Is that possible?

[sushil353]

Hi Kim,

Could you please provide me the sample output.. so that i can try to raplicate it in qlikview.

[#10]

You actually have the right output in your file when you select a date. For example, if I select the date '2013-01-26', i get 24 in the sum of sales. Which is the sum of sales for contract id 1,2 and 3. This is correct, theese are the 3 contracts that have createdate before  '2013-01-26' and enddate after.

But I want to see this sum for every date. Cause you see, if I remove the selection on date, I get 264 in the sum of sales for all dates. Which is not correct. I should still get 24 for the date  '2013-01-26'.