4 Replies Latest reply: Dec 13, 2016 6:15 AM by Sabine Böning RSS

    rolling month and indirect set analysis

    Sabine Böning

      Hello,

       

      I really need your help with a graphic rolling month and indirect set analysis.

       

      The result should show the count per month for calls, but at the end of September we changed the calculation for counting calls.

      That’s why I have to link this two calculations

       

      =
      count({$<TK_MonatJahr= {"$(='<='&Only(date(addmonths(TK_MonatJahr,-2),'MMM-YYYY'))&'>'&Only(date(addmonths(TK_MonatJahr,-7),'MMM-YYYY')))"},
       
      TK_Monat,TK_Status={'Alerting','Pickup'}>}
       
      Distinct callid)
      +
      ((
      count({$<TK_MonatJahr=  {"$(='<='&Only(TK_MonatJahr)&'>'&Only(date(addmonths(TK_MonatJahr,-2),'MMM-YYYY')))"},TK_Monat,id = p({<TK_prevStatus = {'WelcomeAnnouncement'}>}) * p({<TK_Status = {'*'} - {'Announcement','Lost'}>}) >}
      Distinct callid)))

      But for my second calculation it doesn’t show the rolling month, just the selected month.   I think the problem is the indirect set analysis.

       

       

      I suspect the answer is obvious, but I cannot find it!

       

      Thanks,for any help

       

      I really hope, anybody can help.

       

      Thanks and best regards

      Sabine

        • Re: rolling month and indirect set analysis
          Elena Prandoni

          Hi!

          Can you upload a sample qvw?

           

          Thanks,

          Elena

            • Re: rolling month and indirect set analysis
              Sabine Böning

              Hi Elena,

               

              please find attached a sample qvw.

               

              Best regards

              Sabine

                • Re: rolling month and indirect set analysis
                  Elena Prandoni

                  I saw your qvw: can you try with this in the second part of sum?

                   

                  (count({$<TK_MonatJahr= {"$(='<='&Only(date(addmonths(TK_MonatJahr, -0),'MMM-YYYY'))&'>'&Only(date(addmonths(TK_MonatJahr, -2),'MMM-YYYY')))"}

                    ,id = p({<TK_Monat, TK_prevStatus = {'WelcomeAnnouncement'}>}) * p({<TK_Monat, TK_Status = {'*'} - {'Announcement','Lost'}>}), TK_Monat

                  >}

                  Distinct callid))

                    • Re: rolling month and indirect set analysis
                      Sabine Böning

                      Hi Elena,

                       

                      thanks for your help, it seems to work. I really had spent of time for searching a solution.

                       

                      But I have another question, cause my expression is a bit more complicated. I also need to add calls, which have multiple conditions like this.

                       

                      Every calls has only 1 callid, but several ids for each state.

                      I need all Calls, which have:

                      1. id with

                                state (TK_Status) = 'Announcement' and

                                previousstate (TK_prevStatus) ='WelcomeAnnouncement'

                       

                      if this first condition is complied,  the next expression should show

                       

                           2. id with

                                previousstate = 'Announcement' and

                                state = 'Lost' and

                                Duration (TK_Dauer) >=20

                      Plus  Id with

                                previoustate = {'Announcement'} and

                                state = {'*'} - {'Lost'}

                       

                      This is my expression, but I just get the value for the filtered month:

                       

                      (sum
                      ({<
                      TK_Monat,TK_MonatJahr= {"$(='<='&Only(date(addmonths(TK_MonatJahr, -0),'MMM-YYYY'))&'>'&Only(date(addmonths(TK_MonatJahr, -2),'MMM-YYYY')))"}>}
                      aggr(
                      if(
                      aggr
                      (
                      count({$<TK_Monat,TK_MonatJahr= {"$(='<='&Only(date(addmonths(TK_MonatJahr, -0),'MMM-YYYY'))&'>'&Only(date(addmonths(TK_MonatJahr, -2),'MMM-YYYY')))"},
                      id = p({<TK_Monat,TK_prevStatus = {'WelcomeAnnouncement'}>}) * p({<TK_Monat,TK_Status =  {'Announcement'}>})>}
                      Distinct callid),callid)=1,
                      count({<TK_Monat,TK_MonatJahr= {"$(='<='&Only(date(addmonths(TK_MonatJahr, -0),'MMM-YYYY'))&'>'&Only(date(addmonths(TK_MonatJahr, -2),'MMM-YYYY')))"},
                      id = p({<TK_Monat,TK_prevStatus = {'Announcement'}>}) * p({<TK_Monat,TK_Status = {'Lost'}>})* p({<TK_Monat,TK_Dauer = {">=20"}>})>
                      +
                      <
                      id = p({<TK_Monat,TK_prevStatus = {'Announcement'}>}) * p({<TK_Monat,TK_Status = {'*'} - {'Lost'}>}) >}
                      Distinct callid)),callid)))

                       

                      I update my qvw with a table with this expression.

                       

                      Thanks for your help in advance and best regards
                      Sabine