topic Re: Problem with Finding AVG stock values in QlikView

Problem with Finding AVG stock values

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

Hi All,

I am new to QlikVIew, I am facing this issue from past 3 days. I have a chart , where I have to show Avg stock values.

Expression for Avg is :

=$(v_KPI_Stock_24M_Precalc) / if(FISCAL_YEAR=vScript_24MStock_FY0,v_NumMonths_CurrentFY,12).

This is working good, but the problem is , it is showing the values upto current month, i.e-March, but I have to show upto completed month. i.e- Feb. For this I have written expression like.

Expression : =$(v_KPI_Stock_24M_Precalc) -if(FISCAL_YEAR=vScript_24MStock_FY0,$(v_KPI_Stock_24M_Precalc_CurrentMonth),0)

/if(FISCAL_YEAR=vScript_24MStock_FY0,v_NumMonths_CurrentFY,12)..

But, This is not working, I have to create [v_KPI_Stock_24M_Precalc_CurrentMonth] this variable to exclude current month from [v_KPI_Stock_24M_Precalc] this variable.

Attached variables.

loveisfail‌hhajjali‌hic‌kush141087‌mto‌arvind1494‌sureshqv‌omarbensalem‌jontydkpi‌timanshu‌

Please help me Guys.

Re: Problem with Finding AVG stock values

Chanty4u — Mon, 19 Mar 2018 07:46:03 GMT

in only one chart you are restricting the month data till feb or all?

if entire dashboard till feb means you can restrict the data in script.

load...

from []

where month(Datefield)=month(now())-1;

Re: Problem with Finding AVG stock values

ramcena306 — Mon, 19 Mar 2018 07:56:24 GMT

Hi Chanty, I have to show data upto Feb,but it showing current month(un completed month) data also.

I want to exclude current month. Not entire Dashboard only one chart.

Re: Problem with Finding AVG stock values

ramcena306 — Mon, 19 Mar 2018 08:04:09 GMT

Hi All, I will explain Simply,

I want to create an expression or a variable to get the stock values for the latest month and I will substract this value from the result of v_KPI_Stock_24M_Precalc.

how to write expression for that. I mean (v_KPI_Stock_24M_Precalc_CurrentMonth) in this variable,??

Re: Problem with Finding AVG stock values

ramcena306 — Mon, 19 Mar 2018 09:38:50 GMT

Guys any suggestions ??

Re: Problem with Finding AVG stock values

sunny_talwar — Mon, 19 Mar 2018 11:18:27 GMT

This is working good, but the problem is , it is showing the values upto current month, i.e-March, but I have to show upto completed month. i.e- Feb. For this I have written expression like.

Is this because Today is March 19th and once we move to April 1st, you would want to see March's number?

Re: Problem with Finding AVG stock values

ramcena306 — Mon, 19 Mar 2018 11:24:45 GMT

Hi Sunny ,

yes ! but , I want to see data only upto end of the month.(i,e only completed months)

Re: Problem with Finding AVG stock values

ramcena306 — Mon, 19 Mar 2018 11:30:56 GMT

Hi Sunny, I have an approach for this,

if I use a second variable or an expression to get the stock values for the latest month and substract this value from the result of --v_KPI_Stock_24M_Precalc-- I will get the required data.

but I don't have any idea on how to write expression for that, Please help me..

Re: Problem with Finding AVG stock values

sunny_talwar — Mon, 19 Mar 2018 11:35:44 GMT

I mean I am not sure I still understand the whole thing... but why not something like this

If(DateField < MonthStart(Today()), .....)

or something similar in set analysis?

Re: Problem with Finding AVG stock values

ramcena306 — Mon, 19 Mar 2018 12:51:24 GMT

Hi Sunny, any idea on this ??

I want to create a second variable or an expression to get the stock values for the latest month and substract this value from the result of --v_KPI_Stock_24M_Precalc--

but I don't have any idea on how to write expression for that, Please help me..

Re: Problem with Finding AVG stock values

sunny_talwar — Mon, 19 Mar 2018 12:53:39 GMT

It might help if you are able to share a sample where you have been doing your stuff...

Re: Problem with Finding AVG stock values

ramcena306 — Mon, 19 Mar 2018 13:06:30 GMT

Hi sunny, PFA,

variable for 24M_stock, can we create a variable for current month stock like this?

(or) can we exclude latest month in (v_KPI_Stock_24M_Precalc) in this variable ??