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: 
supriyabiware
Creator
Creator

calculating factors in script

Hi All,

I have a requirement is to calculate a column in Script which is as below-

Final Demand Value for Product= Demand Value * Cost * Factor -> (i have demand value and cost already present in table)

whereas my factor is another calculation

Factor (For first Year) = 1

Factor (For 2nd Year) = 1/1.09

Factor (For 3rd Year) = Factor (For 2nd Year)/1.09

Factor (For 4th Year) = Factor (For 3rd Year)/1.09

.

.

.

So on untill the Demand years for one product

How can i calculate the factor column in my script ?

Thanks

Supriya

12 Replies
cweiping
Contributor III
Contributor III

1/pow((1+0.9),year-1)

ex:

1st year-> 1/pow((1+0.9),1-1)->1/pow((1+0.9),0)->1

2nd year-> 1/pow((1+0.9),2-1)->1/pow((1+0.9),1)->1/1.09

3rd year-> 1/pow((1+0.9),3-1)->1/pow((1+0.9),2)->1/1.09/1.09

..

.

.

.

.

.

.

.

10th year->1/pow((1+0.9),10-1)->1/pow((1+0.9),9)->1/1.09/1.09..../1.09

stabben23
Partner - Master
Partner - Master

Hi,

does this fill Your requirement?

supriyabiware
Creator
Creator
Author

perfect, thank you for this

this is how i did it in my script

Sample:

Load Distinct

Product_ID,

Year,

Demand_Value,

Cost

FROM Table

Left Join(Sample)

Load Distinct

Product_ID,

Min(Year) as MinYear

Resident Sample

Group by Product_ID;

FINAL:

Load Distinct *,

if(Product_ID=Previous(Product_ID), 1/pow(1.09,(Year - MinYear))) as FACTOR

Resident Sample

Order By Product_ID;