Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Highlighted
HirisH_V7
Honored Contributor

Last Month According to Data

Dear Qlikers,

According to the below data,

Data latest month.PNG

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

HirisH
“Aspire to Inspire before we Expire!”
1 Solution

Accepted Solutions

Re: Last Month According to Data

Try this:

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

View solution in original post

4 Replies

Re: Last Month According to Data

Try this:

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

View solution in original post

Re: Last Month According to Data

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
Honored Contributor

Re: Last Month According to Data

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




HirisH
“Aspire to Inspire before we Expire!”

Re: Last Month According to Data

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)