Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello there,
Is there a way to reference the value in the last row highlighted in yellow another object preferably a text box.
The requirement is to display the last deposit amount in the last month of the current year but this sample report in the pivot table displays all the deposit in the last month.
I have tried to use the FirstSortedValue(deposit,-Date) but was not desirable.
Essentially my challenge is to get the Max date from the last Month variable ;
vLastMonth =Month(date(AddMonths(max(Date), -1)))
Monthend function also gives me the traditional days but not the exact transactional date the deposit was made.
Please help!.
Why isn't vLastMonth = Month(max(Date), -1) working for you?
You might need to use the aggr function in combination with max & set analysis
That variable returns a month I am more interested in the last deposit ,a date for the matter.
What's the expression of the table in your screenshot.
=SUM({<iYear={'$(vCurrentYear)'},iMonth={'$(vLastMonth)'}>}DISTINCT Deposit)
Try SUM({<iYear={'$(vCurrentYear)'},iMonth={'$(vLastMonth)'}, Date={"=max(Date)"}>} DISTINCT Deposit)..
The expression still gives me the same results as before sums the total deposit in the month instead of ONLY the very last transaction.
Even in a textbox? Try SUM({<iYear={'$(vCurrentYear)'},iMonth={'$(vLastMonth)'}, Date={'25/09/2014'}>} DISTINCT Deposit)
to make sure we're on the right path.
Tried that earlier it work fine when the date inputted. But Date={"=max(Date)" always pick the max date in the current year.
SUM({<iYear={'$(vCurrentYear)'},iMonth={'$(vLastMonth)'}, Date={"=max({<iYear={'$(vCurrentYear)'},iMonth={'$(vLastMonth)'}>} Date)"}>} DISTINCT Deposit) ?
Martin same result.