topic Re: Last Month According to Data in QlikView

Last Month According to Data

HirisH_V7 — Tue, 16 Feb 2016 09:08:29 GMT

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

Re: Last Month According to Data

sunny_talwar — Tue, 16 Feb 2016 09:14:50 GMT

Try this:

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

Re: Last Month According to Data

sunny_talwar — Tue, 16 Feb 2016 09:27:52 GMT

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

Re: Last Month According to Data

HirisH_V7 — Tue, 16 Feb 2016 09:42:21 GMT

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

Re: Last Month According to Data

sunny_talwar — Tue, 16 Feb 2016 09:46:43 GMT

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)