- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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 !
- « Previous Replies
-
- 1
- 2
- Next Replies »
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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))
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi,
I think you made your expression a bit too complicated. The following works:
=sum({$<IndexDate={'$(=Date(vDate))'}>} Sales)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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))))"
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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!)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
=sum({$<IndexDate={'$(=Date(vDate-1))'}>} Sales)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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))
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Perfect. Thank you
- « Previous Replies
-
- 1
- 2
- Next Replies »