8 Replies Latest reply on Jun 30, 2017 10:59 AM by Michael Franz

    Creating a table with values already aggregated.

    Michael Franz

      So I am adding a Distinct Count to Customer #'s over a period of time. I have the following results at the year level

      Does the Distinct Count look at all my customers for the year 2016 and give me a count??


      The 27,357 for 2016 does not make any sense to me.


      Because when I expand the data to its base state, here are the counts. So for example, in the year look I would expect 25,297 for 2016 and would really like 25,005 for 2017 because we are only in June and not reached July so the count is not accurate. The same thing happens for the Quarter look.


        • 1. Re: Creating a table with values already aggregated.
          Deepak Rai

          Michael,try to Use This. If it does n't help. attach a workbook.


          {FIXED [Year]:COUNTD(Customer #)}




          • 2. Re: Creating a table with values already aggregated.
            Michael Gillespie

            This is a guess, much like Deepak's answer, so if you can provide sample data it would help confirm or deny...


            Is your COUNTD at the MONTH level a count of the unique customer numbers that have a transaction associated with them in each of those months?  Or a count of some other metric?


            My suspicion is that there are 27,357 UNIQUE ACCOUNT NUMBERS in whatever field you are counting for the entire year 2017.  The monthly counts are intersecting sets but not identical sets.


            Would that make sense, given what you know about your data?

            • 3. Re: Creating a table with values already aggregated.
              Michael Franz

              So the count is on the month level and each rollup needs to be on the month level.


              Jan 3

              Feb 5

              Mar 4

              Apr 6

              May 9

              Jun 9


              Assume the year is 2017. These are the counts for each month. The


              Year = 7

              Quarter =  Q1 = 4, because of March

                                Q2 = 9 because of June

              Month = the months as displayed above.


              See the excel file I have attached. As you see the customer account changes each month and some are new, returning or left.

              • 4. Re: Creating a table with values already aggregated.
                Michael Gillespie

                OK, I get the Month and Quarter counts, but I don't get the Year count in your example.  How do you get 7?  If I understand what I think you're doing then I'd expect 9 as the 2017 count.


                You want the count of customers for the Latest Period in the sub-dimension, right?  It's a COUNTD, but restricted to a very specific period?


                If that's what you want then the formula will be more complicated.  It's very different from how we would usually do that kind of calculation.

                • 5. Re: Creating a table with values already aggregated.
                  Michael Franz

                  First, you are correct, the year would be 9, not 7.. I changed the file afterwards and forgot to correct the message.


                  Did you see the sample excel data???......


                  So, your statement about a count via restricted to a period is correct it that way because I build the data set that particular way based on prior community advice. I would be more than happy to discuss a different structure, but I think in the old one I ran into the same issues.


                  As you can see from the data set, customers can some and go. We are in insurance and customers have policies that are for a fixed term. If they have a policy from 1/1 to 7/1 and leave us they would be a customer of 1 for Jan, Feb, Mar....Jul... but as of 7/2 they are no longer a customer. So if I look at 7/1/2017 cust = 1, but on 7/2/2017 cust = 0.


                  Any thoughts?

                  • 6. Re: Creating a table with values already aggregated.
                    Amit Narkar

                    With just distinct count at year level it will correctly identify Unique customers irrespective of their term started or ended.

                    Once on the lower level of hierarchies. Each Quarter or Month behaves as separate partition so that level Customers may get counted on multiple occasions.


                    If you just need unique no of customers who got in every month or quarter. You can first calculate a month at which Customer started. Using below logic


                    Customer Term Start Month

                    { FIXED [Customer]: min([Month])}


                    now using Count Distinct of Customers against this would always result in number of unique customers added into system.


                    • 7. Re: Creating a table with values already aggregated.
                      Michael Franz



                      Thanks for all the input, I see how they help, but given the data and presentation, you've helped me move in a different direction with the data. I want to present a chart that will show the change by year, but fixed to the previous month of today(). So if today is 7/1/2017, the 5 year data would be 6/1/17, 6/1/16, 6/1/15, 6/1/14, 6/1/13.... If today is 6/30/2017, then 5/1/17, 5/1/16, 5/1/15, 5/1/14, 5/1/13... Remember the counts are all on day 1 of the month. 1/1xxxx, 2/1/xxxx, 3/1/xxxx


                      I am assuming that I will need to calculated fields.


                      1. Being something to Show or hide based upon the dates

                      2. Someway to get the dates..... I can only think about creating a date by the following, but I think its an ugly thought....

                      • Current Year - somehow looks at today, subtracts 1 month and then goes to the first of that month...
                      • Prior Year
                      • 2 Year ago
                      • 3 Year ago
                      • 4 Year ago
                      • 8. Re: Creating a table with values already aggregated.
                        Michael Franz

                        This should provide me the following to number 1&2 I just described above?


                        I need the one month in arrears due to employees not having everything entered by the end of the month so look one month back.


                        IF((MONTH(TODAY())-1) = MONTH([Book of Business Month])) and (([Book of Business Month])<TODAY())

                        THEN "Show"

                        Else "Hide"



                        The "and" is trying to cut off future dates. Like showing 5/1/18,5/1/19,5/1/20.....


                        But off of this how would use a parameter so they could adjust the months of the years.


                        I would suspect this part of the formula (MONTH(TODAY())-1)  needs to be amended to the parameter?