3 Replies Latest reply on Apr 9, 2016 4:07 AM by Simon Runc

    Count Number of Customers

    Mikey Michaels

      I'm struggling mightily with a table calculation issue.

      You see, I'm trying to present all the Payer Names that have > $25,000 in the >30 Days field. Right now, my viz shows 3, however that is not correct. I tried writing the following calculated field (Payer Count Calculations) to answer my question-

       

      IF ([> 30 Days]) > 25000 THEN [Payer Name]

      END

       

      However, as mentioned before, this does not give me the correct answer. If I look at my source data, I can see the issue. My calculated field only looks at those Payers that have one line item >25,000, I need my calculated field to aggregate all the line item values for the Payer Name and then see if that value is >25,000.

       

      Currently presented by line item value- NOT CORRECT:

       

      CORRECT ANSWER:

       

      Hopefully this is clear Thanks for the help!

       

      Cheers, Mikey

        • 1. Re: Count Number of Customers
          Manideep Bhattacharyya

          Hello Michaels - Following two steps will solve your problem.

           

          Step 1. Create a Calculated field "Payers Group" and the Expression would be

          {FIXED [Payer Name]: sum([> 30 Days])}

          This will aggregate [> 30 Days] value at Payer Name level

           

          Step 2. Drag the field "Payers Group" in the filter and select atleast 25,000Solution.png

           

          Now three Payer Name is getting displayed.

           

          If you find your solution, that mark this as correct answer to close the thread.

          1 of 1 people found this helpful
          • 2. Re: Count Number of Customers
            Mikey Michaels

            Thank you so much for taking a look, Manideep!

            This looks perfect, however my company is still on 8.2 .

            Is there a work around for 8.2?

             

            Cheers,

            Mikey

            • 3. Re: Count Number of Customers
              Simon Runc

              hi Mikey,

               

              First thing to say...your company needs to upgrade!! - not just the new features/calcs...etc. by 9.3 is really really really fast!!

               

              However in the meantime, we can mimic FIXED LoD in 8.2 by using sets...I would definitely watch this TDT on sets (which will explain things far better than me!!)

               

              Think Data Thursday - November 20 - Let's talk about Sets Baby!

               

              So a set (when created as a conditional set) is set against a dimension (in your case Player), and the condition must be an aggregation and boolean...So first we set up a set against [player]

               

              Then we go to the conditional tab, and enter in the following formula

              So, very much like FIXED LoDs, this looks by Player, does a SUM([> 30 Days ]) an checks if this is >25000, and if it is the player (and all their rows) goes in the set, else not.

               

              The great thing with this is that it is dynamic, so as you get more data, if the [player] reaches the 25000 threshold they get added to the set. One word of caution, sets are 'immune' to regular filtering and look over the whole dataset (this is the same for FIXED LoDs), so if you had a date filter (for example) and filtered down to last 2 days, and so no one was >25000 over that period, the [player] would still go in the set, if their entire SUM([> 30 Days])>25000. If you want sets (and FIXED LoDs for that matter) to be filter respondent you need to make the filter 'in context'

               

              Hope this does the trick and makes sense.