Announcements
cancel
Showing results for
Did you mean:
Creator

## Set Analysis Dates Issue

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 !

1 Solution

Accepted Solutions
Partner - Creator III

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))

10 Replies
Partner - Creator III

Hi,

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

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

Creator
Author

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

Master III

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)

Partner - Creator III

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))))"

Creator
Author

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!

Creator
Author

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!)

Master III

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

Partner - Creator III

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))

Creator
Author

Perfect. Thank you

Community Browser