3 Replies Latest reply: Oct 4, 2016 4:19 PM by Vineeth Pujari RSS

    Count columns where value=b

    s Walter

      Hi,

       

      this is the source table:

       

      ProductCheckedPricePacked upCategorySent
      Notebook YXZYes499NoHardwareNo

       

      I create a table in the dashboard like this and want to count all "Yes" and all "No" for each product:

       

      ProductYesNo
      Notebook YXZ12

       

      How can i do it?

       

      regards,

      Fritz

        • Re: Count columns where value=b
          Sunny Talwar

          May be these

           

          Dimension

          Product

           

          Expression

          =SubStringCount(Upper(Checked&' '&Price&' '&[Packed up]&' '&Category&' '&Sent), 'YES')

          =SubStringCount(Upper(Checked&' '&Price&' '&[Packed up]&' '&Category&' '&Sent), 'NO')


          Capture.PNG

          • Re: Count columns where value=b
            Staffan Johansson

            maybe like this:

            in script create flags for each, Checked, Packed up, Sent like;

            if (Checked='Yes',1) as Checked_Yesflag,

            if(Packed up='Yes',1) as PackedUp_Yesflag,

            if(Sent='Yes',1) as Sent_Yesflag,

            if (Checked='No',1) as Checked_Noflag,

            if(Packed up='No',1) as PackedUp_Noflag,

            if(Sent='No',1) as Sent_Noflag

             

            and in Dashboard object expression for YES =sum(Checked_Yesflag+PackedUp_Yesflag+Sent_Yesflag)

            and for No =sum(Checked_Noflag+PackedUp_Nosflag+Sent_Nosflag)

            • Re: Count columns where value=b
              Vineeth Pujari

              Another Scripting Solution

               

              LOAD *

              ,RangeSum(CheckFlag,PackFlag,SentFlag) as Yes's

              ,RangeMissingCount(CheckFlag,PackFlag,SentFlag) as No's

               

              ;

              LOAD *

              ,if(Checked = 'Yes',1,null()) as CheckFlag

              ,if([Packed up] = 'Yes',1,null()) as PackFlag

              ,if(Sent = 'Yes',1,null()) as SentFlag

              INLINE [

              Product,Checked,Price,Packed up,Category,Sent

              Notebook YXZ,Yes,499,No,Hardware,No

              ];

               

               

              countflags.JPG