Assign % to a row

[agbcn]

Hi,

I got a new requirement which is driving me crazy.

I need to calculate the number of days between two dates (already did it) and taking into consideration that number of days, I have to assign a % to that row. Init date will be fixed, but end date can change considering the month of user's choice. For example, the #Days could be 30 if  end date is considered today, but it would be 10 if user choose 20 days before. The % values must be read from an Excel file (I know how to do that) so users can change them if needed. If days are between 0 and 30 days then 90 is assigned. If days are between 31 and 60, then 80 is assigned, and so on ...

To make a long story short, if the values are:

id     #Days     %

1        20          90

2        65          70

3        120       10

What is the best approach? Maybe IntervalMatch?

Thanks in advance

Regards

[jochem_zw]

Something like this (attachment) will do?

[agbcn]

Hi jochem_zw,

Thanks for your reply.

I must be doing something wrong but I can't see anything when I drop the file in Qlik Sense.

Regards

[jochem_zw]

ah sorry it is a qlikview file. to reproduce it, create a table with Id and Days as dimension and this as the measure:

if(Days>=0 and Days<=30
,90
,if(Days>30 and Days<=60
,80
,if(Days>60 and Days<=90
,70))) etc.

[NitinK7]

Hi,

I think you looking like see below screenshot

use Id and days as dimension and write 

expression like

if(Days>=0 and Days<=30 ,90 ,
if(Days>30 and Days<=60 ,80 ,
if(Days>60 and Days<=90 ,70 ,
if(Days>90 and Days<=120,60 ,
if(Days>120 and Days<=150,50,
if(Days>150 and Days<=180,40,
if(Days>180 and Days<=210,30)))))))

[agbcn]

Hi,

Hardcode the intervals and % was my first idea. However, I can't hardcode the intervals as user might decide to change them pretty often. They must be read from an Excel file. Users could change the interval (from 0-30 to 0-45 for instance) and even the %. They need to do this because they need to find the best intervals and % to their business case.

Thanks for your reply and your help.

Regards

[jochem_zw]

as long as the number of intervals is fixed, you could do it by creating an excel with the intervals. and load this excel into the model and assign variables to the interval fields, these variables you can use in your formula. The formula is fixed so the number of intervals also has to be fixed, same for the variables.

[agbcn]

Thanks for your reply.

I'm afraid they will want to change the number of intervals 😓. Let's see if I can make them change their mind!

Regards