8 Replies Latest reply: Jun 27, 2011 3:45 AM by Hans Torsvik RSS

    set-analysis lost customers

    Daniel Viklinder

      Hi,

       

      Im trying to create a set analysis expression to meassure the number of customer that was billed last month but not this month. my expression looks like this:

       

      count({$<CLIENT_NAME={"=sum({$<Month={"*"}, Year={"*"}, [Year Week]={"*"},  [Week]={"*"}, PeriodCounter={"$(#=max(PeriodCounter))"}>} [Bill Amount])>0"}>}distinct CLIENT_NAME)

       

      but it dosent work, does anyone have an idea how i could solve this?

       

      BR

      Daniel

        • set-analysis lost customers

          Hi

           

          If You use Year={"*"} you select all of the years.... Instead you want to disregard the selections in the field.

          And you wanted last months but not this months...

           

          So I would do something like this:

          count(

          {

          $<CLIENT_NAME={"=sum({$<Month=, Year=, [Year Week]=,  [Week]=, PeriodCounter={"$(#=max(PeriodCounter)-1)"}>} [Bill Amount])>0"}>

          *

          $<CLIENT_NAME={"=sum({$<Month=, Year=, [Year Week]=,  [Week]=, PeriodCounter={"$(#=max(PeriodCounter))"}>} [Bill Amount])=0"}>

          }

          distinct CLIENT_NAME)

           

          To SETS... One for the billing last months where Bill Amount > 0 combined with one for current month where Bill Amount =0. They are combined with a Intersection, i.e. Both of the sets must be fullfilled...

           

          BR

          Hans

            • set-analysis lost customers
              Daniel Viklinder

              Hi

              I tried that but the expression gives null:

              =count({$<[Client ID]={"=sum({<PeriodCounter={"$(#=max(PeriodCounter)-1)>}[Bill Amount])>0"}*{"=sum({<PeriodCounter={"$(#=max(PeriodCounter))>}[Bill Amount])=0"}>} distinct [Client ID])

               

              the expression

              sum({<PeriodCounter={"$(#=max(PeriodCounter)-1)>}[Bill Amount])

              and

              sum({<PeriodCounter={"$(#=max(PeriodCounter))>}[Bill Amount])

               

              gives the correct values but when i combine then the result is null, do i need to do an aggr or something like that?

               

              BR

              Daniel

                • set-analysis lost customers

                  Hi,

                  I would go for the Set-Analysis...

                  Where do you use the expression? Graph? Textbox? If Graph, what dimensions...

                   

                  Yours expression, do they work? I miss a second " in the expressions.

                   

                  Do you have an example of your qvw?

                  Everything you write in ={"..."} should work in the searchbox af al list field.

                   

                  So if you use =sum({<PeriodCounter={$(#=max(PeriodCounter)-1)>}[Bill Amount])>0 and sum({<PeriodCounter={$(#=max(PeriodCounter))>}[Bill Amount]) = 0 as a search filter in the ClientId listbox.

                  It should work to use the same expression in a Set analysis statement.

                   

                  count({1<[Client ID]={"sum({1<PeriodCounter={$(#=max(PeriodCounter)-1)>}[Bill Amount])>0 and sum({1<PeriodCounter={$(#=max(PeriodCounter))>}[Bill Amount]) = 0"}>} distinct [Client ID])

                   

                  BR

                  Hans