Hello All, I hope someone can throw some light on this for me. Even if it's to tell me it can't be done. 😉 (Always a challenge)
Find and show HIGHEST value in a dimension within a date range.
I would like to restrict the amount of rows shown on a straight table (Table 2) using set analysis to 1 row per order for a given number of scenario's
- Variables. (not sure if all these are necessary, there may be an easier way of doing this)
//If there is no date selected use the max date in the Date Island
vReportingdate =if(not ISnull(only([Date Island])),only([Date Island]),vMaxDate)
vMaxDate =10/04/2013
vLastUpdated =if(only(%NoOfDaysSinceUpdate)=0,'$(vReportingDate)','<=$(vReportingDate)>$(vReportingDateFrom)')
vLastUpdateLimit =%NoOfDaysSinceUpdate
vReportingDateFrom =Date((vReportingDate - vLastUpdateLimit),'DD/MM/YYYY')
vAmountLimit =%Amount_Limit
//////////////////////////////////////////////////////////////////////
Scenario 1
Default %NoOfDaysSinceUpdate = 0
No Date Island value selected.
No Bookmark
When no date is selected in Date Island I want table '2. Set Expression' to show any orders that have been updated on that date.
If there is and %AmountLimit selected then also select orders below that limit.
The expression used is this and does what it needs to in the scenario.
sum({<
[Update Date] = {'$(vLastUpdated)'},
Amount = {'<=$(vAmountLimit)'}
>}Amount)
//////////////////////////////////////////////////////////////////////
Scenario 2
%NoOfDaysSinceUpdate = 30
No Date Island value selected.
BookMark 10/04/2013 - 30 Days
Show the latest updated version to any orders that have been updated between the max date and the (max date - %NoOfDaysSinceUpdate)
Order1 Version 6 Correct
Order 3 Version 6 Correct
Order 2 Version 3,4,5,6 INCORRECT, I get 4 rows because 4 versions have been updated between <=10/04/2013>11/03/2013
//////////////////////////////////////////////////////////////////////
Scenario 3
%NoOfDaysSinceUpdate = 60
20/03/2013 selected in Date Island
BookMark 20/03/2013 - 60 Days
Show the latest updated version to any orders that have been updated between the max date and the (max date - %NoOfDaysSinceUpdate)
Order1 Version 5 Correct
Order 3 Version 5 Correct
Order 2 Version 1,2,3,4,5 INCORRECT, I get 5 rows because 5 versions have been updated between <=20/03/2013>19/01/2013
//////////////////////////////////////////////////////////////////////
These are just examples to prove the functionality works or not.
The app is attached.
I would be extremely grateful for any help or pointers in the right direction.
Many thanks
Rob
