topic My expression work fine for current month , but i unable to get last month stock amount ? in QlikView

My expression work fine for current month , but i unable to get last month stock amount ?

paulyeo11 — Sun, 14 Apr 2013 15:10:53 GMT

Hi All

I have below expression :-

=Sum({$<YearMonth = {"<=$(=Max({$}[YearMonth]))"}, month = >}[Amount])

which is working fine , because it return the correct stock value.

Now i need to modify the expression so that it will display Jan 2013 stock amount :-

=Sum({$<YearMonth = {"<=$(=Max({$}[YearMonth])-1)"}, month = >}[Amount])

But it does not return the right value , can some one advise me where i go wrong ?

Paul

Re: My expression work fine for current month , but i unable to get last month stock amount ?

kaushiknsolanki — Mon, 15 Apr 2013 06:49:03 GMT

Hi,

Try this expression.

money(

if([Exec P&L Level] = 's','',

Sum({$<YearMonth = {"<=$(=Date(Max({$}[YearMonth])-2,'YY MMM'))"}, month = >}[Amount])
)
, $(vCurrency))

Regards,

Kaushik Solanki

Re: My expression work fine for current month , but i unable to get last month stock amount ?

kaushiknsolanki — Mon, 15 Apr 2013 11:15:32 GMT

Hi,

Try this.

=money(if([Exec P&L Level] = 's','',Sum({$<YearMonth = {"<=$(=Date(addmonths(Max({$}[YearMonth]),-2),'YY MMM'))"}, month = >}[Amount])), $(vCurrency))

Regards,

Kaushik Solanki

Re: My expression work fine for current month , but i unable to get last month stock amount ?

paulyeo11 — Mon, 15 Apr 2013 14:15:23 GMT

Hi Kaushik

Many Thank you for your help , it work fine now. ( Now i understand addmonths ) , now i am sure my project will be successful.

Paul

Re: My expression work fine for current month , but i unable to get last month stock amount ?

paulyeo11 — Mon, 15 Apr 2013 15:11:05 GMT

Hi Kaushik

I wish to plot the stock trend by month . but then i am not able to make X axis display by YearMonth. I have try put diamension with YearMonth it also don't work, may be i need to change the expression into 1 expression , as now i use 1 month 1 expression, below link is my post.

http://community.qlik.com/message/335599#335599

Paul