topic Re: Variable in Script - Assign Value based on If Statement in QlikView

Variable in Script - Assign Value based on If Statement

Anonymous — Wed, 25 Oct 2017 00:00:42 GMT

Hi there,

I'm hoping this is an easy question, but i've never created variables in my script before so just after some help.

I have an average sales calculation in one of my reports that requires a dynamic value as part of the formula.

This value needs to change depending on the current Financial Period (Period 1-12 which represent Months)

Let's name this changing value "vSalesAverageCalc"

I have a variable that has been created in the past, which picks up the current Financial Period (vReportCurrent_Period)

What I need in my script is the following logical statement: (rough idea - I know this incorrect syntax)

If vReportCurrent_Period is 1,2 or 3, Let vSalesAverageCalc = 24

If vReportCurrent_Period is 4,5 or 6 Let vSalesAverageCalc = 15

If vReportCurrent_Period is 7,8 or 9 Let vSalesAverageCalc = 18

If vReportCurrent_Period is 10,11 or 12 Let vSalesAverageCalc = 21

is someone able to help me with this syntax for this variable?

and should the variable already exist in the .qvw or is it created dynamically when the script runs?

Thanks in advance everybody!!

Cheers - Fab

Re: Variable in Script - Assign Value based on If Statement

vishsaggi — Wed, 25 Oct 2017 01:48:23 GMT

May be try this?

Click Ctrl+Alt+V -> It will open variable overview window just add a variable give the name vSalesAverageCalc -> Then add this expr:

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

Re: Variable in Script - Assign Value based on If Statement

Anonymous — Wed, 25 Oct 2017 01:59:42 GMT

Thanks for your input Vishwarath,

that's a great option - haven't done much with variables as yet...

how can I find out what resultant numerical value has been assigned to vSalesAverageCalc?

Re: Variable in Script - Assign Value based on If Statement

Anonymous — Wed, 25 Oct 2017 02:04:43 GMT

I'm also trying to divide a previously loaded dimension by the new variable but it gives me a " requires ')' " error...

How can I call a variable in a divide by statement like below?

ISHRB12WeekAverageQtySales:

Load

ISHRBSKU,

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

Resident ISHRB12WeekAverageQtySales_temp;

Thanks in advance...

Cheers - Fab

Re: Variable in Script - Assign Value based on If Statement

vishsaggi — Wed, 25 Oct 2017 02:05:59 GMT

Create a textbox and add your vSalesAverageCalc variable like

= vSalesAverageCalc

Based on your current period selection the value changes.

Where exactly you want to use this variable i mean in a chart?

Re: Variable in Script - Assign Value based on If Statement

vishsaggi — Wed, 25 Oct 2017 02:07:32 GMT

Can you show how you defining your variable and screenshot of your error?

Re: Variable in Script - Assign Value based on If Statement

Anonymous — Wed, 25 Oct 2017 02:11:40 GMT

That works well Thanks!!

correct Value... we are currently in Period 4 so the result is 15...

I will post some screen shots shortly re the dividing by the variable issue..

Thanks again for your help, really appreciate it...

Fab

Re: Variable in Script - Assign Value based on If Statement

Anonymous — Wed, 25 Oct 2017 02:20:18 GMT

here's my script for that section:

ISHRB12WeekAverageQtySales:

Load

ISHRBSKU,

(ISHRB12WeekAvgQTYSales_temp / $(v12WeekAverageCalc)) as ISHRB12WeekAvgQTYSales

Resident ISHRB12WeekAverageQtySales_temp;

I need to display ISHRB12WeekAvgQTYSales in my pivot chart.

ISHRB12WeekAvgQTYSales_temp is previously loaded in the cript and works fine, I have to divide it by the value of variable v12WeekAverageCalc which you have helped me define above...

I am getting this error:

Re: Variable in Script - Assign Value based on If Statement

Anonymous — Wed, 25 Oct 2017 02:31:08 GMT

Not sure if you need to take a look at this...

Re: Variable in Script - Assign Value based on If Statement

vishsaggi — Wed, 25 Oct 2017 02:31:25 GMT

It will not work like that. The variable inside that Pick how that is changing? What is the expression of vCurrentPeriod variable. Need to write a loop statement i believe for this. Can you share some sample data? I can look into this tomorrow.

Thanks,

Vish.

Re: Variable in Script - Assign Value based on If Statement

Anonymous — Wed, 25 Oct 2017 02:40:14 GMT

Thanks Vish I appreciate that...

you have answered the original question so I will close this thread and mark it as answered...

I will open a new question about this one...

Thanks again!!

Re: Variable in Script - Assign Value based on If Statement

vishsaggi — Wed, 25 Oct 2017 14:47:08 GMT

No problem. And can you post a sample app or guide me to the new post you created.

Re: Variable in Script - Assign Value based on If Statement

Anonymous — Wed, 25 Oct 2017 22:14:12 GMT

Hi Vish,

Thanks a lot for your interest...

here's the new post regarding my issue dividing by the resultant of that variable you help me define.

Divide dimension by Variable in a script

I would like to share a sample QVW, but my script refers to my ODBC database...

Is there a way to create a sample QVW and include some of my data within the qvw instead of trying to pull it down from a database via ODBC?

Thanks

Fab