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

[paulyeo11]

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

[kaushiknsolanki · Accepted solution]

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

[kaushiknsolanki]

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

[paulyeo11]

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

[paulyeo11]

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