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.

[sushil353]

Hi,

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

HTh

Sushil

[#3]

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.

[sushil353]

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

[#5]

Sure, here it is.

[sushil353 · Accepted solution]

Hi kim,,

your issue is resolved..

Please find the attached file. 🙂

HTH

Sushil

[#7]

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