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: 
alec1982
Specialist II
Specialist II

Logic to increase values each year based on a given date

Hi guys,

I have a table with the following data

ID     Amount     Increase Date     Increase PCT

1     1000          01/01/2016               3

2     1200          04/01/2015               2

3     1500          09/01/2015               2

I want to apply a logic to increase the amount by the PCT column once we pass the increase date.. which I have already completed..

the request now is that we need to re-increase the value (Increase PCT *2) next year when we hit the same date again and so on future years..

Any thoughts on how to best build the logic

Best,

Alec

1 Solution

Accepted Solutions
alec1982
Specialist II
Specialist II
Author

I got it figured out using the following logic..

if([Increase Date]>Today(),0,

if([Increase Date]<Today() and Year([Increase Date])=Year(Today()),1,

if([Increase Date]<Today() and Year([Increase Date])<Year(Today()) and num(month([Increase Date]))&num(Day([Increase Date])) <= num(month(Today()))& num(Day(Today())), Year(Today())-Year([Increase Date])+1,

if([Increase Date]<Today() and Year([Increase Date])<Year(Today()) and num(month([Increase Date]))&num(Day([Increase Date]))>   num(month(Today()))& num(Day(Today())),(Year(Today())-Year([Increase Date])))))) as IncreaseFactor

Thank you!

View solution in original post

6 Replies
MK_QSL
MVP
MVP

What is the Date with whom you want to compare?

May be like this?

The logic used below is

     If Date > Today, Amount * 1,

     If Date > Today and Difference is More than Year, Amount * 2.......

etc

etc

====================================

Table:

Load

  *,

  Amount * If(Flag = 'Y', Year(Today()) - Year([Increase Date])+1) as NewAmount;

Load

  ID,

  Amount,

  [Increase Date],

  [Increase PCT],

  If([Increase Date] < Today(), 'Y','N') as Flag

Inline

[

  ID,   Amount,     Increase Date,     Increase PCT

  1,    1000,          10/03/2012,               3

  2,    1200,          10/03/2013,               2

  3,    1500,          10/03/2014,               2

  4,    1800,          10/03/2015,               2

  5,    2000,          20/03/2015,               2

];

alec1982
Specialist II
Specialist II
Author

thank you for the help but I dont think the logic calculate correctly.. for example the first line has increase PCT of 3. and increase date 10/3/2012.. we have passed that date so the amount should be increased by 3% on each year we passed that date which should result to 12% (3 % each year from the date of increase)..

MK_QSL
MVP
MVP

Provide another sample data alongwith the expected output..

or use

Amount * (If(Flag = 'Y', Year(Today()) - Year([Increase Date])+1)/100) as NewAmount;

alec1982
Specialist II
Specialist II
Author

Here is the above data along with the output increase PCT.. once we know that we can multiply with the amount

ID,   Amount,     Increase Date,     Increase PCT   Expected PCT

  1,    1000,          10/03/2012,               3                    12

  2,    1200,          10/03/2013,               2                    6

  3,    1500,          10/03/2014,               2                    4

  4,    1800,          10/03/2015,               2                    2

  5,    2000,          20/03/2015,               2                    0

MK_QSL
MVP
MVP

Which date is the comparing date?

alec1982
Specialist II
Specialist II
Author

I got it figured out using the following logic..

if([Increase Date]>Today(),0,

if([Increase Date]<Today() and Year([Increase Date])=Year(Today()),1,

if([Increase Date]<Today() and Year([Increase Date])<Year(Today()) and num(month([Increase Date]))&num(Day([Increase Date])) <= num(month(Today()))& num(Day(Today())), Year(Today())-Year([Increase Date])+1,

if([Increase Date]<Today() and Year([Increase Date])<Year(Today()) and num(month([Increase Date]))&num(Day([Increase Date]))>   num(month(Today()))& num(Day(Today())),(Year(Today())-Year([Increase Date])))))) as IncreaseFactor

Thank you!