How to get a value with max and if criteria in Qlik Sense?

[Daniel99]

Hi all,

How to get a value with max month and another criteria as below?

=sum(if([CPR_TM1_202202.MONTH]= '2/1/2022' and [CPR_TM1_202202.Business]='CMB', [CPR_TM1_202202.amount]/1000000000))

2/1/2022 is max date in my data set. I want to change '2/1/2022' using the MAX function

any idea?

[vchuprina]

Hi, 

You can use the following expression

SUM({<[CPR_TM1_202202.MONTH]={"$(=Date(max([CPR_TM1_202202.MONTH])))"}, [CPR_TM1_202202.Business]={'CMB'}>}[CPR_TM1_202202.amount]/1000000000)

Regards,

Vitalii

[Daniel99]

Hi Vitalii,

Thanks for your help.

the syntax is no error, but the value not show

Regards,

Daniel

[vchuprina]

Could you send a screenshot of your result?

For instance, my source is following 

Expression:

Result:

Regards,

Vitalii

[Daniel99]

I use this,

=sum(if([CPR_TM1_202202.MONTH]= Max( total [CPR_TM1_202202.MONTH]) and [CPR_TM1_202202.Business]='CMB', [CPR_TM1_202202.amount]/1000000000))

it's worked

[vchuprina · Accepted solution]

Try this, I removed Date function:

SUM({<[CPR_TM1_202202.MONTH]={"$(=max([CPR_TM1_202202.MONTH]))"}, [CPR_TM1_202202.Business]={'CMB'}>}[CPR_TM1_202202.amount]/1000000000)

[Daniel99]

this is work!

if I want to back 1 month for max date, should I put ,-1? 

example:

SUM({<[CPR_TM1_202202.MONTH]={"$(=max([CPR_TM1_202202.MONTH]),-1)"}, [CPR_TM1_202202.Business]={'CMB'}>}[CPR_TM1_202202.amount]/1000000000)

[vchuprina]

It will work with max([CPR_TM1_202202.MONTH], 2)

[Daniel99]

not -1?

if max is 2/2/2022 then i need to substract with -1, right?

[vchuprina · Accepted solution]

if you use the date max(date)-1 will return the previous day.

So for YYYYMM to get  you can use max(yearmonth, 2) - previous month (second biggest value)

for MM/DD/YYYY you can use date(max(date)-1)  - previous day

max function returns values in numeric format, so it's important to convert value back to date format.