Skip to main content
Announcements
Qlik Connect 2024! Seize endless possibilities! LEARN MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
Daniel99
Contributor II
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?

=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?

Labels (5)
3 Solutions

Accepted Solutions
vchuprina
Specialist
Specialist

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)

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").

View solution in original post

vchuprina
Specialist
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").

View solution in original post

Daniel99
Contributor II
Contributor II
Author

ahhh i seee.

really appreciate all solutions from you!

thank you Vitalli!

 

Regards,

Daniel

 

View solution in original post

11 Replies
vchuprina
Specialist
Specialist

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

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").
Daniel99
Contributor II
Contributor II
Author

Hi Vitalii,

Thanks for your help.

the syntax is no error, but the value not show

Daniel99_0-1650886867122.png

Regards,

Daniel

vchuprina
Specialist
Specialist

Could you send a screenshot of your result?

For instance, my source is following 

vchuprina_0-1650887575441.png

Expression:

vchuprina_1-1650887632242.png

Result:

vchuprina_2-1650887669757.png

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").
Daniel99
Contributor II
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

vchuprina
Specialist
Specialist

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)

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").
Daniel99
Contributor II
Contributor II
Author

this is work!

Daniel99_0-1650892978604.png

 

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
Specialist
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").
Daniel99
Contributor II
Contributor II
Author

not -1?

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

 

 

vchuprina
Specialist
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").