Set analysis, with if statement

[Anonymous]

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

[MK_QSL]

EDITED : after Jonathan's reply 🙂

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

[jonathandienst]

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.

[jonathandienst · Accepted solution]

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)

)

or

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

  Sum({<Headcount_Current_Flag>} FTE),

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

)