Scripting help

[#1]

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.

[IAMDV]

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

[#3]

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

Thanks for your help,

Saurav

[IAMDV]

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

[IAMDV · Accepted solution]

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

[IAMDV]

Hi Saurav,

Please can you mark this thread as answered or make it private discussion. It will be easy for other members to see the answer.

Thanks,

DV

[#7]

Thank you!