Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
i have an expression like this in my straight table, i wanted to exclude all the selections even if a month year is selected and at the same time i wanted to use sum of leaves for max of end date.
Expression:
Test = sum({$<Year=, pay_fortnight= , FinacialYear= snap_pay_end_date=>}if((snap_pay_end_date)='$(=max({$<Year=, pay_fortnight=>}snap_pay_end_date))',excess_leave_days)
)
when i did this , i was getting values only for the selection period not for all months. But i want the same for all the months regardless of selections.
Thanks in advance for your help.
Regards
Karthik
try this
sum({$<Year=, pay_fortnight= , FinacialYear= , snap_pay_end_date=,
snap_pay_end_date={"$(=date(max(snap_pay_end_date)))"}>}excess_leave_days)
or in the script
left join(YourTable)
LOAD MonthYear,
date(max(snap_pay_end_date)) as snap_pay_end_date
1 as MaxDateFlag
Resident Your Table
Expression
sum({$<Year=, pay_fortnight= , FinacialYear= , snap_pay_end_date=,MaxDateFlag ={'1'}>}excess_leave_days)
Try including the set in max() too, like:
sum({$<Year=, pay_fortnight= , FinacialYear= ,snap_pay_end_date={"$(=date(max({1}snap_pay_end_date)))"}>}excess_leave_days)
You might want to replace {1} with your time dimensions, if you wish to ignore the selection for only them. I.e. -
sum({$<Year=, pay_fortnight= , FinacialYear= , snap_pay_end_date={"$(=date(max({<Year=, pay_fortnight= , FinacialYear=,>}snap_pay_end_date)))"}>}excess_leave_days)
Thanks a lot for your reply, i have tried this already. But i want to try the same using expressions.
I have Month as a dimension, so i would like to write an expression to get the value for the last pay fortnight value for that each months. and i need to exclude all the selection from it.
What about something like this:
FirstSortedValue({$<Year=, pay_fortnight=, FinacialYear=, snap_pay_end_date=>} Aggr(Sum({$<Year=, pay_fortnight=, FinacialYear=, snap_pay_end_date=>}excess_leave_days), snap_pay_end_date), -snap_pay_end_date)