Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Now Live: Qlik Sense SaaS Simplified Authoring – Analytics Creation for Everyone: READ DETAILS
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

1 Solution

Accepted Solutions
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

View solution in original post

12 Replies
Anil_Babu_Samineni

What is the formulae for Factor ??

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
supriyabiware
Creator
Creator
Author

Hi,

These are constants depending on demand years for one product

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

Anil_Babu_Samineni

Not really sure, your expression about? You haven't indicate anything. For a while perhaps this?

Sample:

Load [Demand Value], Cost, Year From table;

Final:

Load 'This year' as Flag,

     "Factor Formulae" as Factor

Resident Sample Where Year = 2017;

Concatenate(Final)

Load 'This year' as Flag,

     "Factor Formulae"/2017 as Factor

Resident Sample Where Year = 2016;

Concatenate(Final)

Load 'This year' as Flag,

     "Factor Formulae"/2016 as Factor

Resident Sample Where Year = 2015;

Legend:

NoConcatenate

Load [Demand Value], Cost, Sum(Flag) as Factor_Sum Resident Final Group By [Demand Value], Cost;

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
supriyabiware
Creator
Creator
Author

Thanks for your reply,

Perhaps the concatenation wouldnt possible since my sample Data table is 25 million rows. (i am not sure though)

Also for every product i have various number for years depending on demand contract(eg - one product has been signed for next 10 years, another for 2 years, etc)

Sample:

Load

[Demand Value],

Cost,

Year

From table;

Final table be something like

Final:

Left Keep(Sample)

Year,

If (Year= min Year, 1 , Previous('Factor')/1.09 ) as Factor

resident Sample

Group by Year;

But the factor formula doesnt seem quite working. it should be running calculation on itself

Thanks

Supriya

supriyabiware
Creator
Creator
Author

Maybe i missed one thing, My Sample Data be like

Sample:

Load

ProductID,

[Demand Value],

Cost,

Year

From table;

Anil_Babu_Samineni

May be this?

Sample:

Load ProductID, [Demand Value], Cost, Year From table;

LET vMinYear = Min(Year);

Final:

Left Keep (Sample)

Year,

If (Year= $(vMinYear), 1 , Previous('Factor')/1.09 ) as Factor

resident Sample

Group by Year;

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
supriyabiware
Creator
Creator
Author

Thanks Anil, but i think Previous(Factor) isnt working here since it is not getting any value. Even Same with PEEK

it was just an assumption for solution.

Also minimum year for every product would be different

Anil_Babu_Samineni

In fact, Not sure how Factor is calculating? Will you attach sample data to investigate

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
supriyabiware
Creator
Creator
Author

Something like this -

i just need to calculate Factor column as shown in expected result