Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Hi,
please find attached and see if it is what you are looking for.
HTh
Sushil
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.
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
Sure, here it is.
Hi kim,,
your issue is resolved..
Please find the attached file. 🙂
HTH
Sushil
Thanks alot! That was easier than i expected You really saved my day.