topic Scripting help in QlikView

Scripting help

Thu, 17 May 2012 19:23:53 GMT

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

IAMDV — Thu, 17 May 2012 20:38:24 GMT

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,

Re: Scripting help

Thu, 17 May 2012 20:40:54 GMT

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

IAMDV — Thu, 17 May 2012 20:47:24 GMT

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,

www.QlikShare.com

Re: Scripting help

IAMDV — Mon, 21 May 2012 15:54:53 GMT

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,

www.QlikShare.Com

Re: Scripting help

IAMDV — Tue, 22 May 2012 08:17:06 GMT

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,

Re: Scripting help

Tue, 22 May 2012 12:12:06 GMT

Thank you!