Announcements
cancel
Showing results for
Did you mean:
Contributor II

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

Hi all,

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

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

any idea?

Labels (5)

• ### Variables

3 Solutions

Accepted Solutions
Specialist

Try this, I removed Date function:

Press LIKE if the given solution helps to solve the problem.
If it's possible please mark correct answers as "solutions" (you can mark up to 3 "solutions").
Specialist

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.

Press LIKE if the given solution helps to solve the problem.
If it's possible please mark correct answers as "solutions" (you can mark up to 3 "solutions").
Contributor II
Author

ahhh i seee.

really appreciate all solutions from you!

thank you Vitalli!

Regards,

Daniel

11 Replies
Specialist

Hi,

You can use the following expression

Regards,

Vitalii

Press LIKE if the given solution helps to solve the problem.
If it's possible please mark correct answers as "solutions" (you can mark up to 3 "solutions").
Contributor II
Author

Hi Vitalii,

the syntax is no error, but the value not show

Regards,

Daniel

Specialist

Could you send a screenshot of your result?

For instance, my source is following

Expression:

Result:

Regards,

Vitalii

Press LIKE if the given solution helps to solve the problem.
If it's possible please mark correct answers as "solutions" (you can mark up to 3 "solutions").
Contributor II
Author

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

Specialist

Try this, I removed Date function:

Press LIKE if the given solution helps to solve the problem.
If it's possible please mark correct answers as "solutions" (you can mark up to 3 "solutions").
Contributor II
Author

this is work!

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

example:

Specialist

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

Press LIKE if the given solution helps to solve the problem.
If it's possible please mark correct answers as "solutions" (you can mark up to 3 "solutions").
Contributor II
Author

not -1?

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

Specialist

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.

Press LIKE if the given solution helps to solve the problem.
If it's possible please mark correct answers as "solutions" (you can mark up to 3 "solutions").