10 Replies Latest reply on Mar 8, 2018 6:39 PM by Fikri Shihab

    Count values (and calculate percentage of total) per month

    Christopher Beavan

      Hello,

       

      I am trying to make a graph showing the percentage of shipments that met their target date vs those that did not for each month. Each row in the data is a shipment with a "target date" and a "actual date". I first made a calculated field to calculate the difference between the dates. Then I made a calculated field to mark differences > 0 as "Late" and <= 0 as "Met". So now each row has a column with either "Late" or "Met" in it. The name of this calculated field is "Met SLT?"

       

      Now I want to know what the percentage of "Met" shipments are compared to the total number of shipments for each month (as shown in the excel table in the first picture:

      Screen Shot 2015-02-06 at 2.57.10 PM.jpg

       

      As far as I have gotten is getting a count of the "Met SLT" values per month (shown in the second picture):

      Screen Shot 2015-02-06 at 2.58.30 PM.jpg

       

      This seems like a pretty simple thing maybe using table calculations, but I have not been able to figure it out. Any help would be great. Thank you in advance!

        • 1. Re: Count values (and calculate percentage of total) per month
          Jeff James

          Christopher,

           

          It would be helpful it you could provide a packaged workbook that contains non-proprietary data.

           

          It is much easier to work with your starting point than to discuss in abstract.

          • 2. Re: Count values (and calculate percentage of total) per month
            daniel.stokes

            Hi Christopher!

             

            If my assumption of the data being used is correct from the description given and screenshot provided.

             

            Where Met SLT along with filtering out the false results, is only a count of the shipments that have arrived in a timely manner, we still have the overall data on the shipments arrival dates, we can take this dimension and use it to create a percentage of the total:

             

                      COUNT([Met SLT?])  / COUNT([Actual Arival])

             

            Alternatively, we can use the Table Calculations Total and Window_Sum. I have attached a sample workbook illustrating this method.

             

            Using Window_Sum, we can total up the all the dimensions that meet the condition:

             

            WINDOW_SUM(

                IF DATEDIFF() <= 0 THEN 1

                ELSE 0

                END

            )

             

            then divide by the total:

             

            TOTAL(COUNT([Actual Dates]))

             

             

            Hope this helps!

            • 3. Re: Count values (and calculate percentage of total) per month
              Christopher Beavan

              Hi Daniel,

               

              Thank you for the quick answer! I did your first suggestion of making a calculated field for the percentage, and it worked perfectly. I am going to read up more on table calculations anyways though, they seem very useful, but I dont have a good grasp on them.

               

              Thanks again,

              Chris

              • 4. Re: Count values (and calculate percentage of total) per month
                Fikri Shihab

                Hello All,

                 

                I have similar question, but slightly different flavor.  I have a bunch of data showing users and latency values. Similar like above if latency is below x and I marked it as "good", otherwise "bad". So I have two groups of values, good an bad every period of time out of total number of users (let's say 1000 users).

                 

                I'd like to create a chart showing percentage of total good and bad value using area chart so that the combined good and bad is always 100%, but instead of using number of records I'd like the total is based on total number of users. 

                 

                I was able to create area chart correctly using number of records but some reasons couldn't figure out how to show percentage based on number of users.

                Any help or suggestion would be helpful...  Thank you in advance

                • 5. Re: Count values (and calculate percentage of total) per month
                  Jeff James

                  Fikri,

                   

                  Countd (count distinct) is the calculation you want to base it on the number of users.

                   

                  Countd is a non-additive measure, so it gets "weird" for the total if you have users that fit in both "good" and "bad". In that case, you would get over 100% because the sum of good and bad can be bigger than the total. For example, using your 1000 users from above, you could have 560 good and 540 bad resulting in 110%.

                   

                  If that's the problem, you can solve it by dividing each value by window_sum(countd([user])) instead of total(countd([user]))

                   

                  Jeff

                  • 6. Re: Count values (and calculate percentage of total) per month
                    Fikri Shihab

                    Hi Jeff,

                     

                    Thanks for your suggestion. I just managed to come back to this issue again.  In fact, I used count distinct already.
                    Perhaps a bit more detail on the scenario. Let's say I have total user of 1000 distinct users registered in the system, and at any time only about 800 users are active.
                    Within these active users, I have made two groups based on certain criteria, which results in good and bad; number of good and bad terminals vary over the time.

                    What I'd like to do is to create stacked area chart with 100 % as the total users active  (Y-axis) at one time (normally around 800-850 users).

                    So, let's say at one sample interval (let's assume 15 min), active terminals are 800 users, in which 700 are considered good and 100 are bad. I'd like to show a stacked area  chart with (7/8) is good and 1/8 s bad. The combined stacks is always 100%.

                     

                    I don't seem to be able to show the percentage of users on line at one time as part of on line users properly with area chart that shows the combined on line users is always 100%.
                    Is there a way to do this or is this not even valid?


                    If this is not possible,  how do I show a stacked chart  with active users portion and total user portion?

                     

                    I tried countd(user)/Total(countd(user) to show the portion of active user and another measure with simply Total(countd(user)) but can't make them stacked each other with 100%. Any suggestion and advice?

                     

                    Thank you,

                    • 7. Re: Count values (and calculate percentage of total) per month
                      Jeff James

                      OK. I think I understand the problem now. You still need countd(), but the table calculation is done per cell to get the area chart.

                       

                      I've attached an example where you can see the total values are different time period. Screenshot:

                       

                      • 8. Re: Count values (and calculate percentage of total) per month
                        Fikri Shihab

                        Hi Jeff,

                         

                        Thank you for looking into this. I think you described it correctly and also your chart showed that too.

                         

                        I also tried to do exactly as in your attached example; using countd, percent of total and compute using cell but they did not stack at 100%. I changed the time period granularity also from continuous to discrete and vice versa. None gave me the result I want.


                        This is the chart that I've got

                         

                         

                        Please note that total active users may vary from time to time, so I don't know if this is the root cause of the issue.  If I used number of records as the basis for countd I was able to get the 100% stacked, but I'd like to see the percentage of total users active rather than number of records.

                        • 9. Re: Count values (and calculate percentage of total) per month
                          Jeff James

                          Can you post a sample packaged workbook with the data embedded? Working with the actual data will make it a lot simpler to diagnose.

                           

                          Any sensitive columns can be blanked out. They won't impact the calculations.

                          • 10. Re: Count values (and calculate percentage of total) per month
                            Fikri Shihab

                            Hi Jeff,

                             

                            I managed to get this working using exactly the same calculation per your example and my previous chart. The only difference is I had to change it different time interval (e.g. 5 minutes), which the interval of the original data.

                             

                            Since number of data I was using was quite large so I used time intervals to visualize the data using  the following formula

                             

                            DATEADD('minute',

                                    (INT(DATEPART('minute', [Timestamp]) / 15)) * 15,

                                    DATETRUNC('hour', [Timestamp]) )

                             

                            for 15 minutes interval and so on so forth with various intervals.

                             

                            When I used these intervals it could not get 100%, but when using 5 minutes this worked.  It is still a mystery to me, but I think I have a work around for this.

                            I'll see if i can provide a sample packaged showing this peculiarity.

                             

                            Thanks,

                            Fikri