4 Replies Latest reply on Jan 17, 2017 9:28 AM by Jamieson Christian

    Calculating CountD

    Jilani Shaik

      Hi

       

      How to calculate the no of id's in each and every region

       

      if the id is same in both regions i want to calculate only from North Region and dont include id from south in that count.

      id is diff include both id's in that count.

       

      Plz help me the logic with out using LOD's

        • 1. Re: Calculating CountD
          Jim Dehner

          Try an LOD expression like

           

          { FIXED [Region]: Countd([Order ID])}

           

          This should work for you

          Please let me know

           

          Jim

          • 2. Re: Calculating CountD
            Jilani Shaik

            i want to calculate only unique id's in both regions

            i dont want to calculate same id's in both regions.

            • 3. Re: Calculating CountD
              Joshua Milligan

              Jliani,

               

              I'm not sure I completely follow your requirements.  Are you saying you only want to count an ID if it is unique to the region (doesn't appear in any other regions) or that you do want to count IDs that occur in multiple regions, but only once in the North region?

               

              If the first case, then it's fairly easy:

              Just filter out any IDs where the count of distinct regions is not 1 (i.e. the ID occurs in more than one region) and then show the distinct count of ID per region

               

               

               

              But in the second case, where you want to force it to count for North, then it gets a bit more complex (I'll use Central as the "default" region in this example).  You'll have a couple of options. LoD really is likely going to be the easiest option (not sure why you indicated you don't want to use it).  If it really is a problem, let me know.

               

              The calculation would look something like this:

               

              COUNTD(

              IF

                  ([Region] == "Central" AND {FIXED [ID] : MIN([Region])} == "Central")

                  OR

                  {FIXED [ID] : COUNTD([Region])} == 1

              THEN [ID]

              END

              )

               

               

              The basic idea is that at a row level we see if the region is central and that for the ID the min region is also central (the Min works because Central is first alphabetically as is North in your sample data).  Also, we'll count an ID in any region (including Central) if it is the only region.  Here's how it looks with ID in the view:

               

               

              IDs 1,2,3, and 5 get counted once in the Central region.  4 gets counted once in the East region (the only region in which it occurs).

               

              You can remove ID from the view and get the correct counts:

               

               

              Just remember that using FIXED LoD means that any filters you want applied prior to the calculation need to be added to context.  Or, you can rewrite the calc to use nested INCLUDE and EXCLUDE.

               

              Hope that helps!

              Joshua

              • 4. Re: Calculating CountD
                Jamieson Christian

                Jilani,

                 

                You're going to have to use LODs for this exercise. It's unavoidable.

                 

                Create a calculated field [Modified Order ID] to check for the existence of [Order ID] in more than one region, and if so, implicitly NULL it out except where it appears in the North region.

                 

                [Modified Order ID]

                IF { FIXED [Order ID] : COUNTD([Region]) } = 1 OR [Region] = 'North' THEN [Order ID] END

                 

                Then use [Modified Order ID] on your ROWS shelf instead of [Order ID].

                 

                (Disclaimer: the above is untested because you did not attach a sample workbook to try it on.)