2 Replies Latest reply: Aug 7, 2018 10:43 AM by Nizam HM RSS

    Identifying the count based on between condition

    Nizam HM

      Hi  All,

       

      I am working around the thing where I want to identify the number of record present between the condition

      my condition looks like these

       

      =if((Sum({<[Pricing Level]={'BC Level'}>} [w/M])

      >= Sum({<[Pricing Level]={'Strategic'}>} [w/M])-sum([w/M])*0.2)=-1,count( total 1))


      and i am trying to implement in KPI or text box

      any help will be helpful

        • Re: Identifying the count based on between condition
          Sunny Talwar

          Which is the dimension you are actually trying to count?

            • Re: Identifying the count based on between condition
              Nizam HM

              Hi Sunny

               

              Lets say i have 1000 records out of that  70 records are matching this condition and i have few more  condition  then i have to get the count of that particular record .lets say the dimension is Pricing Level to get the count

               

              expression what i used

               

              =if(Sum({<[Pricing Level]={'BC Level'}>} [w/M])=0,'BLANK',

               

               

              if(Sum({<[Pricing Level]={'BC Level'}>} [w/M])>=Sum({<[Pricing Level]={'Strategic'}>} [w/M]-[w/M]*0.2)and

                  Sum({<[Pricing Level]={'BC Level'}>} [w/M])<=Sum({<[Pricing Level]={'Strategic'}>} [w/M]+[w/M]*0.2),'Comply',

                 

                  if(Sum({<[Pricing Level]={'BC Level'}>} [w/M])>=Sum({<[Pricing Level]={'Strategic'}>} [w/M]-12)and

                  Sum({<[Pricing Level]={'BC Level'}>} [w/M])<=Sum({<[Pricing Level]={'Strategic'}>} [w/M]+12),'Comply',

                      

              if((Sum({<[Pricing Level]={'BC Level'}>} [w/M])<Sum({<[Pricing Level]={'Strategic'}>} [w/M]-[w/M]*0.2)or

                  Sum({<[Pricing Level]={'BC Level'}>} [w/M])<Sum({<[Pricing Level]={'Strategic'}>} [w/M]-12))and

                  Sum({<[Pricing Level]={'Strategic'}>} [w/M])<>0,'Lower',

               

                  if((Sum({<[Pricing Level]={'BC Level'}>} [w/M])>Sum({<[Pricing Level]={'Strategic'}>} [w/M]+[w/M]*0.2)or

                  Sum({<[Pricing Level]={'BC Level'}>} [w/M])>Sum({<[Pricing Level]={'Strategic'}>} [w/M]+12))and

                  Sum({<[Pricing Level]={'Strategic'}>} [w/M])<>0,'Upper',

                 

                  if(Sum({<[Pricing Level]={'Strategic'}>} [w/M])=0 and

                  Sum({<[Pricing Level]={'BC Level'}>} [w/M])>=Sum({<[Pricing Level]={'Strategic'}>} [w/M]),'Upper',

                 

                  if(Sum({<[Pricing Level]={'BC Level'}>} [w/M])>=Sum({<[Pricing Level]={'Strategic'}>} [w/M]+[w/M]*0.2)and

                  Sum({<[Pricing Level]={'Strategic'}>} [w/M])<>0,'Upper','Comply'

                 

                 

                  )))))))

               

              What I am trying to do is In KPI I am trying to show the individual numbers  based on the [Comply ,Upper,Lower,Blank]

              Due to some aggretation problem i am not getting the answer just getting null value.