Problem with Finding AVG stock values

[ramcena306]

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.

[Chanty4u]

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;

[ramcena306]

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.

[ramcena306]

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,??

[ramcena306]

Guys any suggestions ??

[sunny_talwar]

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?

[ramcena306]

Hi Sunny ,

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

[ramcena306]

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..

[sunny_talwar]

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?

[ramcena306]

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..