Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

hirishv7
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

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)

4 Replies

Re: Last Month According to Data

Try this:

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

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

hirishv7
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




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)

Community Browser