# QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Not applicable

## 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.

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.

Tags (1)
1 Solution

Accepted Solutions
Luminary

## Re: Scripting help

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

6 Replies
Luminary

## Re: Scripting help

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

Not applicable

## Re: Scripting help

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

Saurav

Luminary

## Re: Scripting help

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

Luminary

## Re: Scripting help

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

Luminary

## Re: Scripting help

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

Not applicable

## Re: Scripting help

Thank you!

Community Browser