6 Replies Latest reply: May 22, 2012 8:12 AM by Saurav S RSS

    Scripting help

      I am trying to figure out how to prorate a certain metric. The way it works is, we prorate the time customer service representatives are available to take phonecalls in a quarter vs. the total time there are at work since some of theirtime is allocated to doing projects. I have figured out the how to prorate inmonth 1 but I am not sure how to prorate in the months going forward.

       

      Here is what I am trying to figure out:

       

      In January, the prorate would be Prorate/1

       

      In February, (Month1 Prorate+ Month2 Prorate)/ 2

       

      March, (Month1 Prorate+ Month2 Prorate)+ Month3 Prorate/3

       

      In Q2 the prorate resets to month 1 again and it becomesprorate/1 and so on.

       

       

      Additional info:

       

      1) I am trying to calculate this in the scrip not directly in the chart, because I needto use the prorate to discount other metrics.

       

      2)TheYearMonth is in a string format.

       

      3) Thereare multiple YearMonth dimensions because they correlate to different reps.

       

        • Re: Scripting help
          Deepak Vadithala

          Hi Saurav,

           

          I'm assuming that this question is linked to your previous question? And how do you wanted to how the output? Do you want to show a straight table or any specific object?

           

          Thanks,

          DV

            • Re: Scripting help

              Yes this is linked to my previous questions. I want to show the data in a straight table.

               

              Thanks for your help,

               

              Saurav

                • Re: Scripting help
                  Deepak Vadithala

                  Saurav,

                   

                  Please can you share the raw data in excel file? because I can't open QVW files from my home system as I have personal edition at home. Please give the original raw data which you shared in the first file.

                   

                  Regards,

                  DV

                  www.QlikShare.com

                  • Re: Scripting help
                    Deepak Vadithala

                    Hi Saurav,

                     

                    Sorry for the delay in getting back to you. I think, I have got it working but I would advice you to test it thoroughly before implementing in the final application. I have to add some stuff in the script and then you need to use this expression :

                     

                    SUM(IF(MyUniqueNumber = 1,

                        Aggr(SUM({< MyUniqueNumber = {1}>}  Prorate), MyQuarterNumber, Rep_Name),

                            IF(MyUniqueNumber = 2,

                                Aggr(SUM({< MyUniqueNumber = {"<=2"}>}  Prorate), MyQuarterNumber, Rep_Name),

                                    Aggr(SUM({< MyUniqueNumber = {"<=3"}>}  Prorate), MyQuarterNumber, Rep_Name)

                                )

                            )   

                        )

                    /MyUniqueNumber

                     

                    Please see the script for more understanding. I have added Quarter Number, Unique Number (1,2,3) within each Quarter and Year Number.

                     

                    I hope it makes sense.

                     

                    Please let me know if you have any questions.

                     

                    Cheers,

                    DV

                    www.QlikShare.Com