topic Re: Divide dimension by Variable in a script in QlikView

Divide dimension by Variable in a script

Anonymous — Wed, 25 Oct 2017 03:03:02 GMT

Hi all,

This question is a continuation of a previous thread: Re: Variable in Script - Assign Value based on If Statement

I have a variable v12WeekAverageCalc that has been defined as follows: (Thanks to Vish's help )

= Pick(vReportCurrent_Period, 24, 24, 24, 15, 15, 15, 18, 18, 18, 21, 21, 21)

This picks up the current financial period (defined in vReportCurrent_Period) from my database and assigns a predefined numerical value based on the current financial period.

Period 1,2,3 = 24

Period 4,5,6 = 15

Period 7,8,9 = 18

Period 10,11,12 = 21

I need to use these numerical values to calculate average QTY sales based on a number of months...

vReportCurrent_Period is defined as follows in another part of my script:

Period:

LOAD "fin_year" as FinancialYear,

"Period" as FinancialPeriod;

SQL SELECT "fin_year", "Period"

FROM PUB.perlive

where Module ='iv';

LET vReportCurrent_FinYear = FieldValue('FinancialYear',1);

LET vReportCurrent_Period = FieldValue('FinancialPeriod',1);

DROP TABLE Period;

Both v12WeekAverageCalc and vReportCurrent_Period are giving the correct output.

Now I need to use v12WeekAverageCalc in a calculation as follows.

QTYSalesValueX / v12WeekAverageCalc = 12WeekAvgQTYSales

here's my actual script for that section:

ISHRB12WeekAverageQtySales:

Load

ISHRBSKU,

(ISHRB12WeekAvgQTYSales_temp / $(v12WeekAverageCalc)) as ISHRB12WeekAvgQTYSales

Resident ISHRB12WeekAverageQtySales_temp;

I need to display ISHRB12WeekAvgQTYSales in my pivot chart.

I am getting this error:

Can anyone help??

Thanks in advance...

Cheers,

Fab

Re: Divide dimension by Variable in a script

vishsaggi — Wed, 25 Oct 2017 23:41:14 GMT

Can you upload the sample here i will see what i can do. I do not need to run the data from database. Or if you can load all your fields you are pulling from your database and use table box to add all fields and send that to excel. Then upload your excel file if possible.

Re: Divide dimension by Variable in a script

Anonymous — Wed, 25 Oct 2017 23:45:11 GMT

OK Vish,

Give me some time and I will do that shortly...

thanks again for your help...

Fab

Re: Divide dimension by Variable in a script

vishsaggi — Wed, 25 Oct 2017 23:52:03 GMT

No problem.

Re: Divide dimension by Variable in a script

Anonymous — Thu, 26 Oct 2017 01:34:24 GMT

Hi Vish,

I have attached a sample QVW and the data exported to XLS from a table object within my qvw. Thanks for suggesting that...

Sorry for the delay, this is the first time i've created a sample qvw...

Just for your info, the final calculation i'm trying to achieve is:

(((ISHRBPreviousYTDSaleTotals + ISHRBCurrentYTDSaleTotals) * 3) / $(v12WeekAverageCalc))

you can scroll down to the bottom of the script where i was trying to get this to work..

I have replaced $(v12WeekAverageCalc) with 15 for testing purposes

here is the excerpt fom that part of the script...

////////////////////////////////////////////////////////Calculate 12 Week Average//////////////////////////////////////////////////

LEFT KEEP (ISHRBYTDQTYSales)

ISHRB12WeekAverageQtySales_temp:

Load

ISHRBSKU,

((ISHRBPreviousYTDSaleTotals + ISHRBCurrentYTDSaleTotals) * 3) as ISHRB12WeekAvgQTYSales_temp

Resident ISHRBYTDQTYSales;

ISHRB12WeekAverageQtySales:

Load

ISHRBSKU,

(ISHRB12WeekAvgQTYSales_temp / 15) as ISHRB12WeekAvgQTYSales

//(ISHRB12WeekAvgQTYSales_temp / $(v12WeekAverageCalc)) as ISHRB12WeekAvgQTYSales

Resident ISHRB12WeekAverageQtySales_temp;

//DROP TABLE ISHRB12WeekAverageQtySales_temp;

//Left Join (ISHRBYTDQTYSales)

//Load

//ISHRBSKU,

//(Round)ISHRB12WeekAvgQTYSales

//Resident ISHRB12WeekAverageQtySales;

//DROP TABLE ISHRB12WeekAverageQtySales;

Re: Divide dimension by Variable in a script

vishsaggi — Thu, 26 Oct 2017 19:00:09 GMT

Need sometime to look into this. Ok.?

Re: Divide dimension by Variable in a script

Anonymous — Thu, 26 Oct 2017 23:35:26 GMT

No probs Vish,

I appreciate any help I get, not at work today, but i'll be back on this on Monday... cheers...

Fab

Re: Divide dimension by Variable in a script

vishsaggi — Fri, 27 Oct 2017 01:09:32 GMT

Ok try these two scripts.

After below left keep.

We have to do some changes to the variables. Like Ctrl+Alt+V

In your variable overview just change exactly to what you can see in the screen shot below.

Pick(Match($(i), 1,2,3,4,5,6,7,8,9,10,11,12), 24, 24, 24, 15, 15, 15, 18, 18, 18, 21, 21, 21)

Pick($(i), 24, 24, 24, 15, 15, 15, 18, 18, 18, 21, 21, 21)

LEFT KEEP (ISHRBYTDQTYSales)

FOR i = 1 to $(vReportCurrent_Period)

ISHRB12WeekAverageQtySales_temp:

Load

ISHRBSKU,

((ISHRBPreviousYTDSaleTotals + ISHRBCurrentYTDSaleTotals) * 3)/$(v12WeekAverageCalc) as ISHRB12WeekAvgQTYSales_temp

FROM [HRB Sample data.xls]

(biff, embedded labels, table is Sheet1$);;

Next;

FOR i = 1 to $(vReportCurrent_Period)

ISHRB12WeekAverageQtySales:

Load

ISHRBSKU,

(ISHRB12WeekAvgQTYSales_temp /$(v12WeekAverageCalc) ) as ISHRB12WeekAvgQTYSales

//(ISHRB12WeekAvgQTYSales_temp / $(v12WeekAverageCalc)) as ISHRB12WeekAvgQTYSales

Resident ISHRB12WeekAverageQtySales_temp;

Next;

Let me know how it goes.

Re: Divide dimension by Variable in a script

Anonymous — Mon, 30 Oct 2017 01:53:31 GMT

Hi Vish,

Thank you so much for your help...

I have taken your suggestions and tried a few things...

but this seems to have worked:

I've changed the v12WeekAverageCalc variable value to:

Pick(Match($(vReportCurrent_Period), 1,2,3,4,5,6,7,8,9,10,11,12), 24, 24, 24, 15, 15, 15, 18, 18, 18, 21, 21, 21)

And amended the script as follows:

LEFT KEEP (ISHRBYTDQTYSales)

ISHRB12WeekAverageQtySales_temp:

Load

ISHRBSKU,

((ISHRBPreviousYTDSaleTotals + ISHRBCurrentYTDSaleTotals) * 3) as ISHRB12WeekAvgQTYSales_temp

Resident ISHRBYTDQTYSales;

ISHRB12WeekAverageQtySales:

Load

ISHRBSKU,

(ISHRB12WeekAvgQTYSales_temp /$(v12WeekAverageCalc) ) as ISHRB12WeekAvgQTYSales

Resident ISHRB12WeekAverageQtySales_temp;

DROP TABLE ISHRB12WeekAverageQtySales_temp;

It looks like it's working correctly... (I even tested changing the results to make sure it is working)

We will be going into a new financial period on wednesday, so we'll be able to run more checks...

Thanks again for your invaluable input...

Fab

Re: Divide dimension by Variable in a script

vishsaggi — Mon, 30 Oct 2017 13:53:41 GMT

Cool sounds good. Cos i was not sure your expected output and also cant run the scripts so just gave what i tried. Good you figured it out and I am glad it worked for your with little changes. Please close the thread accordingly.

Re: Divide dimension by Variable in a script

Anonymous — Mon, 30 Oct 2017 21:51:14 GMT

Thanks again Vish,

would have never got it to work without your input...

Much appreciated...

Cheers,

Fab