Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Prorate

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 phone calls in a quarter vs the total time there are at work since some of their time is allocated to doing projects. I have figured out the how to prorate in month 1 but I am not sure how to prorate in the months going forward. I am pretty sure there is an easy solution but I since I am new to Qlikview I am not sure.

Here is what I am trying to figure out:

In January, the prorate would be Prorate/1

In February, Prorate/ 2

March, Prorate/3

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

Tags (1)
1 Solution

Accepted Solutions
Luminary
Luminary

Re: Prorate

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

10 Replies
mphekin12
Valued Contributor

Re: Prorate

You could add a mapping table like this:

ProrateMap:

Mapping Load * INLINE [

     Month, Prorate

     'Jan',1

     'Feb',2

     'Mar',3

     'Apr',1

     'May',2

     'Jun',3

     'Jul',1

     'Aug',2

     'Sep',3

     'Oct',1

     'Nov',2

     'Dec',3

];

Then add this line to your master table:

     Prorate / ApplyMap('ProrateMap',right(YearMonth,3)) as Calculation

I hope this helps.

Luminary
Luminary

Re: Prorate

Hi,

I have made following assumptions to workout an example :


1. Assuming that you don't want to caculate in the script and you wanted to directly use the chart for YearMonth Dimension

2. There are multiple Prorate values for each YearMonth Dimension, so I'm using Sum() as aggregation but you can change it to whatever is applicable

3. Assuming that YearMonth field is a string and not in date format. So I'm converting the field to numeric value.

Here is the expression :

sum(Prorate) / IF( Mod(Num(Month(date#(Right(YearMonth, 3),'MMM'))), 3) = 0, 3, Mod(Num(Month(date#(Right(YearMonth, 3),'MMM'))), 3))

Logic :

  • Converting the YearMonth field to month numbers (numeric values)
  • Using the Mod() function to calculate the logic. Example : Jan = 1, Feb = 2, Mar = 3, Apr = 1, May = 2...

And I'm also attaching the QVW file for your reference. I hope it makes sense.

Good luck!

Cheers,

DV

www.QlikShare.com

Not applicable

Re: Prorate

This is very helpful!!

In response to the assumptions that you made:

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

2) There are multipleYearMonth dimensions because they correlate to different reps . I did not include the agent information because I didnt think it was necessary. However, I have included reps for your reference.

3) TheYearMonth is in a string format.

Does anything change now?

Thanks,

Not applicable

Re: Prorate

The numerator changes each month as well.

For example

Month 1= Prorate/1

Month2= (Month1 Prorate+ Month2 Prorate)/2

Month3= (Month1 Prorate + Month2 Prorate + Month3 Prorate)/3

Any Ideas?

Thanks,

Luminary
Luminary

Re: Prorate

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

Not applicable

Re: Prorate

Here it is.

Luminary
Luminary

Re: Prorate

Thanks Saurav. I'll get back to you tomorrow. I hope it's okay.

Thanks,

DV

Not applicable

Re: Prorate

Works for me. Please let me know if you have any updates.

Again, thank you for all of your help.

Saurav

Luminary
Luminary

Re: Prorate

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