7 Replies Latest reply on Jul 30, 2015 8:23 AM by Ben Page

    Aggregate - non aggregate problem

    manu gupta

      Hi,

       

      I want to calculate a metric over the last 6 month of my data.

      Metric= Sum(col1)/sum(col2) from 7/31/2014 to 12/31/2014

      when I create a calculated field to perform this calculation, I get an error message of "cant mix aggregate and non- aggregate fields".

      If by using ATTR() function, i resolve this error, but then also my purpose is not solved as the value is not getting calculated.

       

      In the workbook attached, I have a Max_Month and v_lat_6Month as calculated field. And I want to calculate v_calc as described above.

      Please help.

       

       

       

      Regards,

      Manu

        • 1. Re: Aggregate - non aggregate problem
          Ben Page

          I think I see the issue--your comparison withing v_calc is the problem because

          " if ATTR([Month]) < [Max_Month] " is never going to equate to true. I've laid out all of your fields on the row shelf to try to demonstrate what's happening. You need to change the logic to:

           

          " if ATTR([Month]) <= [Max_Month] " for the condition to work. I've done this within the v_calc UPDATE field in my workbook.


          Alternatively, you could simply remove the "If" condition altogether (so that the field was just sum(col1)/sum(col2), plot it against the Month variable, and then filter then months that you'd like to see. I think this would give you the same result.


          Let me know if this is helpful or correct. Thanks,

          Ben

          • 2. Re: Aggregate - non aggregate problem
            manu gupta

            Hi Ben,

             

            Thanks for the response. But I still didn't found the exact answer. Could you please look into this again.

             

            Problem:

            1. I want to calculate the "sum(col1)/sum(col2)" for the last 6 months i.e. Max_Date and 5 months before that.

            2. Also, the application you shared with me didn't had the the Max_Month as Fixed. It was changing for every month. I want that to be fixed then only I would be able to compare the rest of the months with the Max_Month.

             

             

            Regards,

            Manu

            • 3. Re: Aggregate - non aggregate problem
              Ben Page

              Ahh I see. I've made some modifications to the workbook--

               

              1. I've made Max_Month fixed on the greatest of all the months. I changed the calculation to Window_Max rather than just Max(). You need to make sure to compute this along Month for it to work correctly.

               

              2. To show the last 6 months of data, I've modified v_calc UPDATE so that the formula is simply Sum(col1)/Sum(col2). From there, I updated the v_Lat_Month field to operate as a boolean filter. The code is like this:

               

              v_Lat_month field.PNG

               

              I think this gives you your desired result, but I'm not positive so please let me know if this is helpful or correct. Thanks,

              Ben

              1 of 1 people found this helpful
              • 4. Re: Aggregate - non aggregate problem
                manu gupta

                Hi Ben,

                 

                Sorry for replying late. The answer was really helpful. I was actually posting in bits n pieces so as to get a clue and solve the problem. But unfortunately I am not able to solve my problem.

                It would be really helpful if you could help me with the problem.

                 

                Problem:

                we need to create a chart(may be donut/ bullet punch or any other chart to show KPI) that displays data for the last six months i.e. if we have data from Jan 2014 to Dec 2014, the aggregated data for Dec 2014 to Jul 2014 should be displayed ( formula=sum(col1/col2) ). If now user selects Nov 2014 from the Quick filter then the aggregated data for Nov 2014 to Jun 2014 and so on.

                 

                Thanks

                • 5. Re: Aggregate - non aggregate problem
                  Ben Page

                  Hi Manu,

                   

                  Try this. I've created a parameter where you select the final month. So if you select December 2014, you will see July 2014- December 2014. If you select Nov 2014, you'll see June 2014 - Nov 2014. Note that if you select a month with less than 6 months before it, you'll only see those months instead of the full previous six months. So, if you select March 2014, you'll see January 2014 - March 2014. Is this what you're looking for? I've attached a workbook.

                   

                  Ben

                  • 6. Re: Aggregate - non aggregate problem
                    manu gupta

                    Thanks Ben, it worked. I was almost there but missed the use of Parameter. Thank you very much.

                    • 7. Re: Aggregate - non aggregate problem
                      Ben Page

                      Glad I could help