Last Month According to Data

[HirisH_V7]

Dear Qlikers,

According to the below data,

I need to acquire the latest Month Sales means which is not '0',

I tried Expression like this,

=Sum({<MonthNum={'$(=Max(MonthNum))'},[Entry Type]={'Sales'}>}Value)

But its not working!!

It should be like this,

=Sum({<MonthNum={'6'},[Entry Type]={'Sales'}>}Value) like this,

Thanks in advance,

PFA,

Hirish

[sunny_talwar · Accepted solution]

Try this:

=Sum({<MonthNum={"$(=Max({<[Entry Type]={Sales}, Value = {'>0'}>}MonthNum))"},[Entry Type]={'Sales'}>}Value)

[sunny_talwar]

To make this future proof you might want to consider using Date field instead of MonthNum. The reason i say this is because when you have 2016, Max(MonthNum) may still equal 12 if you have last year's December data, but Date will include year information and will always show the max based on the year and month.

=Sum({<Date={"$(=Date(Max({<[Entry Type]={Sales}, Value = {'>0'}>}Date)))"},[Entry Type]={'Sales'}>}Value)

Just something to think about

[HirisH_V7]

Yes for that i m Writing Like this,

Sum({<MonthNum={"$(=Max({<[Entry Type]={Sales}, Value = {'>0'}>}MonthNum))"},[Entry Type]={'Sales'},Year={'$(=Max(Year))'}>}Value)

But your expression looks fine Sunny

[sunny_talwar]

MonthNum won't consider the Max(Year) unless you tell it:

Sum({<MonthNum={"$(=Max({<[Entry Type]={Sales}, Value = {'>0'}, Year = {'$(Max(Year))'}>}MonthNum))"},[Entry Type]={'Sales'},Year={'$(=Max(Year))'}>}Value)