Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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!
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
];
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)..
Provide another sample data alongwith the expected output..
or use
Amount * (If(Flag = 'Y', Year(Today()) - Year([Increase Date])+1)/100) as NewAmount;
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
Which date is the comparing date?
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!