topic Re: Ignore selections and include max of date in QlikView

Ignore selections and include max of date

karthikdaibala — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Ignore selections and include max of date

Kushal_Chawda — Tue, 19 Apr 2016 06:49:45 GMT

try this

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

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

Re: Ignore selections and include max of date

Kushal_Chawda — Tue, 19 Apr 2016 06:53:37 GMT

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)

Re: Ignore selections and include max of date

tresB — Tue, 19 Apr 2016 06:56:01 GMT

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)

Re: Ignore selections and include max of date

karthikdaibala — Tue, 19 Apr 2016 21:14:13 GMT

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

Re: Ignore selections and include max of date

karthikdaibala — Tue, 19 Apr 2016 21:16:10 GMT

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.

Re: Ignore selections and include max of date

sunny_talwar — Wed, 20 Apr 2016 00:59:18 GMT

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)