topic Re: Set Analysis expression for previous month details in QlikView

Set Analysis expression for previous month details

Fri, 20 Nov 2020 18:02:30 GMT

Hello

I converted below pivot table to a straight table and next to the closing stock, i want to add up closing stock for previous month (i.e. if the diagram shows Feb 2014 figures, i want a new field 'Closing stock for previous month' reflecting data for Jan 2014 for which i gave expression as

Sum({<Year={"=$(=max(Year))"},Month_Name=,Month={"<=$(=Max(Month)-1)"}>}[Closing Stock(Unit)])

It gives me incorrect result, how can it be achieved?

Thanks

Re: Set Analysis expression for previous month details

Anonymous — Wed, 08 Oct 2014 16:15:48 GMT

try with

=sum({<Month_Name={"$(AddMonths(Month,-1))"}>} [Closing Stock(Unit)])

Marc.

Re: Set Analysis expression for previous month details

Thu, 09 Oct 2014 09:38:21 GMT

doesnt give any result

Re: Set Analysis expression for previous month details

Anonymous — Thu, 09 Oct 2014 09:41:49 GMT

Could you please attach a sample file?

Marc.

Re: Set Analysis expression for previous month details

PrashantSangle — Thu, 09 Oct 2014 09:51:21 GMT

Hi,

As you say you want Closing stock of Previous Month then

Month={"<=$(=Max(Month)-1)"}

Condition wont work for you

Since you have to write

Month={"$(=Max(Month)-1)"}

then your expression became

Sum({<Year={"=$(=max(Year))"},Month_Name=,Month={"$(=Max(Month)-1)"}>}[Closing Stock(Unit)])

Regards

Re: Set Analysis expression for previous month details

Anonymous — Thu, 09 Oct 2014 09:59:22 GMT

Add an equal sign:

=sum({<Month_Name={"$( = AddMonths(Month,-1))"}>} [Closing Stock(Unit)])

Marc.

Re: Set Analysis expression for previous month details

Thu, 09 Oct 2014 10:00:58 GMT

Thanks, but i have already used it, it doesnt give me correct figures

Re: Set Analysis expression for previous month details

jyothish8807 — Thu, 09 Oct 2014 10:08:02 GMT

Hi,

Try this:

Sum({<Year=,Month_Name=,Month={"$(=Date((Max(Month)-1),'MM-YY')"}>}[Closing Stock(Unit)])

Regards