3 Replies Latest reply: Jul 6, 2015 5:24 AM by Jonathan Dienst RSS

    Set analysis, with if statement

    Phil Speight

      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
          Manish Kachhia

          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
            Jonathan Dienst

            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
                Jonathan Dienst

                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)

                )