topic Re: Set analysis, with if statement in QlikView

Set analysis, with if statement

Anonymous — Mon, 06 Jul 2015 09:08:34 GMT

I have a a pie chart that shows how many FTE are split between Frontline & Support.

My data is imported each month, and I have a flag which identifies if the month is the latest month. I have created a set analysis for this (Below):

=(Sum({<Headcount_Current_Flag={'Y'}>} FTE))

How do I update the set analysis that will only use the flag if no 'date_of_headcount' selections have been made, but uses the 'date_of_headcount' selection if a 'date' has been selected.

Any help appreciated.

Thanks

Phil

Re: Set analysis, with if statement

MK_QSL — Mon, 06 Jul 2015 09:17:10 GMT

EDITED : after Jonathan's reply 🙂

If(GetSelectedCount(date_of_headcount) > 0, Sum({<Headcount_Current_Flag={'Y'}>} FTE), Sum(FTE))

Re: Set analysis, with if statement

jonathandienst — Mon, 06 Jul 2015 09:20:44 GMT

I prefer to use a simple Max(Month_sequence) type of function - this will get the latest loaded if nothing is selected and the selection when something is selected.

in your case, use the flag conditionally, you could do this:

If(GetSelectedCount(date_of_headcount) > 0,

Sum(FTE),

Sum({<Headcount_Current_Flag={'Y'}>} FTE)

)

This will work ONLY for selections in date_of_headcount. Selections in related date fields will not work.

Re: Set analysis, with if statement

jonathandienst — Mon, 06 Jul 2015 09:24:47 GMT

If the selection is actually in another field (eg Month or Year), then you will need something like:

If(GetPossibleCount(date_of_headcount) < FieldValueCount(date_of_headcount),

Sum(FTE),

Sum({<Headcount_Current_Flag={'Y'}>} FTE)

)

If(GetPossibleCount(date_of_headcount) < FieldValueCount(date_of_headcount),

Sum({<Headcount_Current_Flag>} FTE),

Sum({<Headcount_Current_Flag={'Y'}>} FTE)

)