Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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.

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.

1 Solution

Accepted Solutions
IAMDV
Luminary Alumni
Luminary Alumni

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

View solution in original post

6 Replies
IAMDV
Luminary Alumni
Luminary Alumni

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
Author

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

Thanks for your help,

Saurav

IAMDV
Luminary Alumni
Luminary Alumni

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
Luminary Alumni
Luminary Alumni

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
Luminary Alumni
Luminary Alumni

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
Author

Thank you!