topic Re: Use date as reference in set analysis in QlikView

Use date as reference in set analysis

Wed, 17 Apr 2013 12:24:04 GMT

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.

Re: Use date as reference in set analysis

Gysbert_Wassenaar — Wed, 17 Apr 2013 17:44:25 GMT

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.

Re: Use date as reference in set analysis

Thu, 18 Apr 2013 05:55:06 GMT

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?

Re: Use date as reference in set analysis

Thu, 25 Apr 2013 05:14:51 GMT

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.

Re: Use date as reference in set analysis

sushil353 — Thu, 25 Apr 2013 05:33:38 GMT

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

Re: Use date as reference in set analysis

Thu, 25 Apr 2013 05:49:34 GMT

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

Re: Use date as reference in set analysis

sushil353 — Thu, 25 Apr 2013 06:04:33 GMT

Hello Kim.

Please find the attached modified file.

HTH

Sushil

Re: Use date as reference in set analysis

Thu, 25 Apr 2013 06:10:43 GMT

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?

Re: Use date as reference in set analysis

sushil353 — Thu, 25 Apr 2013 06:15:47 GMT

Hi Kim,

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

Re: Use date as reference in set analysis

Thu, 25 Apr 2013 06:20:23 GMT

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

Re: Use date as reference in set analysis

sushil353 — Thu, 25 Apr 2013 06:33:53 GMT

Hi,

please find attached and see if it is what you are looking for.

HTh

Sushil

Re: Use date as reference in set analysis

Thu, 25 Apr 2013 06:41:01 GMT

I'm afraid not. You were very close with the first example

Let's say I want to create a bar chart with date as dimension at the botton. Then I would want to see 24 for the date 2013-01-26 which is what you have. And then I also want to see 12 which is the value for 2013-01-17. This would let me see how the sales of active contracts changes over time. If I do this in your examples, it looks like this:

It has the value 264 for all Date, which is wrong. The sum of sales for active contracts is not 264 for every date. It should vary over time.

Re: Use date as reference in set analysis

sushil353 — Thu, 25 Apr 2013 06:54:13 GMT

Ok.

i got your issue..

your issue can be resolved using interval match function.

could you please provide me

ework test.xlsx file so that i can load data at my local...

--Sushil

Re: Use date as reference in set analysis

Thu, 25 Apr 2013 07:06:18 GMT

Sure, here it is.

Re: Use date as reference in set analysis

sushil353 — Thu, 25 Apr 2013 07:15:04 GMT

Hi kim,,

your issue is resolved..

Please find the attached file. 🙂

HTH

Sushil

Re: Use date as reference in set analysis

Thu, 25 Apr 2013 07:26:48 GMT

Thanks alot! That was easier than i expected You really saved my day.