Ignore selections and include max of date

[karthikdaibala]

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

[Kushal_Chawda]

try this

sum({$<Year=, pay_fortnight= , FinacialYear= , snap_pay_end_date=,

snap_pay_end_date={"$(=date(max(snap_pay_end_date)))"}>}excess_leave_days)

[Kushal_Chawda]

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)

[tresesco]

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)

[karthikdaibala]

Thanks a lot for your reply, i have tried this already. But i want to try the same using expressions.

[karthikdaibala]

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.

[sunny_talwar]

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)