topic Re: calculating factors in script in QlikView

calculating factors in script

supriyabiware — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: calculating factors in script

Anil_Babu_Samineni — Mon, 11 Dec 2017 09:36:23 GMT

What is the formulae for Factor ??

Re: calculating factors in script

supriyabiware — Mon, 11 Dec 2017 09:39:10 GMT

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

Re: calculating factors in script

Anil_Babu_Samineni — Mon, 11 Dec 2017 09:47:52 GMT

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;

Re: calculating factors in script

supriyabiware — Mon, 11 Dec 2017 10:13:52 GMT

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

Re: calculating factors in script

supriyabiware — Mon, 11 Dec 2017 10:19:23 GMT

Maybe i missed one thing, My Sample Data be like

Sample:

Load

ProductID,

[Demand Value],

Cost,

Year

From table;

Re: calculating factors in script

Anil_Babu_Samineni — Mon, 11 Dec 2017 11:31:38 GMT

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;

Re: calculating factors in script

supriyabiware — Mon, 11 Dec 2017 11:45:32 GMT

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

Re: calculating factors in script

Anil_Babu_Samineni — Mon, 11 Dec 2017 11:46:46 GMT

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

Re: calculating factors in script

supriyabiware — Mon, 11 Dec 2017 12:13:53 GMT

Something like this -

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

Re: calculating factors in script

cweiping — Mon, 11 Dec 2017 12:55:56 GMT

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

Re: calculating factors in script

stabben23 — Mon, 11 Dec 2017 13:42:38 GMT

Hi,

does this fill Your requirement?

Re: calculating factors in script

supriyabiware — Mon, 11 Dec 2017 14:28:49 GMT

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;