Set Analysis Dates Issue

[Karl_Hart]

All,

I have a requirement to show all transactions on the last date before a selected date. So I have a variable vDate which the user can select.

I then calculated the last date before the selected date

     Max({<IndexDate = {'<$(=Date($(vDate)))'}>}IndexDate))

I think want to use this within set analysis to sum the sales on that date

     sum({$<IndexDate={"=$(=Max({<IndexDate = {'<$(=Date($(vDate)))'}>}IndexDate))"}>} Sales)

Please see attached. This works when I put less than or greater than in the expression but not equals.

     sum({$<IndexDate={">=$(=Max({<IndexDate = {'<$(=Date($(vDate)))'}>}IndexDate))"}>} Sales)

     sum({$<IndexDate={">=$(=Max({<IndexDate = {'<$(=Date($(vDate)))'}>}IndexDate))"}>} Sales)

My questions:

     1. Can anyone explain why its not working or correct my "equals" formula

     2. Also, if there is a better way of doing this, I'm open to suggestions

I've attached a simple example, as ever the loaded data is vastly more complicated

Thanks,

Karl !

[niclaz79]

Hi,

I think you made your expression a bit too complicated. The following works:

=sum({$<IndexDate={'$(=Date(vDate))'}>} Sales)

[Karl_Hart]

Niclas,

Thank you for your reply. Your expression works if I wanted to look at the sales on the selected date. The added level of complication is becuase I want to look at the sum of the sales on the last day BEFORE the selected date.

So for example, I might select 31st Dec and so this would give me the last sales of the year (for each product / company / region etc etc)

Thanks

[sasiparupudi1]

If you want the sum of sales to be returned before the selected index date then

=sum({$<IndexDate={'<=$(=Date(Max(IndexDate)))'}>} Sales)

otherwise

sum({$<IndexDate={'$(=Date(vDate))'}>} Sales)

[niclaz79]

Hi,

Do you mean that you want to see the date on the selected day - 1 or all the days before selected date for the current year? For the first example, use $(=Date(vDate-1)). For the second, use "<=$(=Date(vDate-1)) > $(=YearStart(Date(vDate))))"

[Karl_Hart]

It will be different depeding on the customer / region etc so it needs to be able to calculate the last transaction date before the selected date. For one customer it may be the day before, for another it may be 2 months before!

[Karl_Hart]

Thanks, but I need it to go off a variable, rather than a selected date. Also, I just need the sales amount for the transcations on one date, not every date before the variable (That bit works in my example but its not what I need!)

[sasiparupudi1]

=sum({$<IndexDate={'$(=Date(vDate-1))'}>} Sales)

[niclaz79 · Accepted solution]

Hi again,

I was not able to get the set-analysis to function as expected, however using an if-statement accomplishes the same thing:

Sum(if(IndexDate = $(=Max({<IndexDate = {'<$(=Date(vDate))'}>}IndexDate)), Sales))

[Karl_Hart]

Perfect. Thank you