topic Re: How to exclude latest month from variable? in QlikView

How to exclude latest month from variable?

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

Hi All,

PFA variable, I have a variable which shows 24M (Months)stock values.

how to adjust this variable to exclude the latest month?

stalwar1‌loveisfail‌sureshqv‌jontydkpi‌arvind1494‌kush141087‌

Re: How to exclude latest month from variable?

Chanty4u — Tue, 20 Mar 2018 06:37:48 GMT

add this into your variable

Month -= {"$(=Date(MonthStart(Today()), 'Format'))"}

Re: How to exclude latest month from variable?

ramcena306 — Tue, 20 Mar 2018 06:51:19 GMT

hi Chanty, Thanks for the Quick response, But, it's not working for me.

when I use this variable in a chart Expression it's showing 'No data to Display'

Re: How to exclude latest month from variable?

ramcena306 — Tue, 20 Mar 2018 06:54:16 GMT

Otherwise, I want to create a new variable to get the stock values for the latest month by using this variable.

How can we do this?

Re: How to exclude latest month from variable?

PrashantSangle — Tue, 20 Mar 2018 06:59:41 GMT

are you using alternate state??

why you required scope resolution operator in your expression?

Regards,

Re: How to exclude latest month from variable?

shiveshsingh — Tue, 20 Mar 2018 07:05:38 GMT

=Sum({<Year = {"$=(max(year))"} , Month = {"$=(Date(Max(Month),'MMM'))"} >}Data)

You can use this to get last month sales

Re: How to exclude latest month from variable?

ramcena306 — Tue, 20 Mar 2018 07:08:28 GMT

Hi Prashant,

Yes we are using Alternate state.

Actually I have to show stock values upto completed months only,

for that, if I create a second variable or an expression to get the stock values for the latest month and then I will substract this value from the result of first variable. Then I will get the required Output.(Because the attached variable giving current month stock values also)

Re: How to exclude latest month from variable?

ramcena306 — Tue, 20 Mar 2018 07:12:25 GMT

Hi Shivesh Singh,

This is not my requirement.

Re: How to exclude latest month from variable?

shiveshsingh — Tue, 20 Mar 2018 07:13:29 GMT

You can put this in variable, you wrote there to get lastmonth sales.right?

Re: How to exclude latest month from variable?

ramcena306 — Tue, 20 Mar 2018 07:25:11 GMT

Hi Shivesh Singh,

Not Last month, to get latest month.

Re: How to exclude latest month from variable?

jyothish8807 — Tue, 20 Mar 2018 07:33:19 GMT

Try this:

sum({1<

CALYEAR= {"$=(max(year))"} , CALMONTH={*}-{"$=(Date(Max(Month),'MMM'))"}

,PROD_CALMONTH=$::PROD_CALMONTH

,PROD_CALYEAR=$::PROD_CALYEAR

>}MON24)

I would recommend to use a Date filed instead of using Year and month field.

You have define if you are using "Prod_Cal" month or just "cal month" fields for your analysis.

Br,

Re: How to exclude latest month from variable?

sasiparupudi1 — Tue, 20 Mar 2018 09:30:33 GMT

Try something like below

v_KPI_Stock_24M_Precalc ::

sum(

//basic calculation for 24 month stock based on precalculated MON24

//fields to be considered for selections

{1<

CALMONTH=$::CALMONTH

,CALYEAR=$::CALYEAR

,PROD_CALMONTH=$::PROD_CALMONTH

,PROD_CALYEAR=$::PROD_CALYEAR

>-1<CALMONTH={"$(=Date(MonthStart(Today()), 'MM'))"},CALYEAR={"$(=Year(Today()))"}>

}

MON24)