18 Replies Latest reply: Aug 20, 2015 8:30 AM by Sunny Talwar

# max() function for charts

Dear Community,

I'm tring to use the max() function combinated with count().

max([date import]) : gives me the date

Count(ID): gives me the number of ID

And what I want is to count only the ID when [date import] is max. So I tried different things:

Count(if( [date import]= max([date import]), ID))

Count({<max([date import])>} ID)

... It didn't work...

I'm sure it's possible ! thanks for your help

Best regards

• ###### Re: max() function for charts

Try this:

=Count({<[date import] = {"\$(=Date(Max([date import]), 'DateFormatHere'))"}>}ID)

• ###### Re: max() function for charts

Or may be this:

Count(If([date import]= Max(TOTAL [date import]), ID))

• ###### Re: max() function for charts

Hi Sunny,

Both fonction work. But I tried to use it in an other chart and I get the following problem. As you can see in the first chart I was counting the ID for each Station. For each station you can have several dates ([date import]). And what I did priviously because I wasn't able to use max() is to count all the ID and divided it by the number of distinct [date import]: Count(ID)/(count(distinct([date import]))).

So I tried both answers you send me because I'm more interested by the number of ID at max([date import]) for each Station instead of my average calculation. But it seems that Qlik will first look for the max([date import]) and then look for the station. The result is on the second picture where you can see that there is only one point.

So how to tell Qlik that for each station I want to look for the max([date import]) and then count the ID. instead of looking for the max([date import]) count the ID and then look for the station?

• ###### Re: max() function for charts

See if this works:

Count(If([date import]= Max(TOTAL <Station> [date import]), ID))

• ###### Re: max() function for charts

Qlik tells me there is an error in the expression. I tried a few changes but no results so far. I will dig a little more and see what I can get. Anyway I don't want to bother you for this, you have been more than helpfull!

Many thanks again Sunny !

• ###### Re: max() function for charts

Can you share a screenshot of the error you are seeing when you use the expression I gave you? Include the expression and the error message both.

Best,

Sunny

• ###### Re: max() function for charts

Station is called "Activité" in my data base. Tool is in french

• ###### Re: max() function for charts

Is Activité one of your dimension? It seems that it will only work if it is one of the dimension: https://help.qlik.com/sense/2.0/en-US/online/#../Subsystems/Hub/Content/ChartFunctions/BasicAggregationFunctions/sum.htm…

• ###### Re: max() function for charts

Yes "Activité" is the first dimension and the values can be (ST59, ST50, ST40, etc...)

• ###### Re: max() function for charts

I added some bracket and the expression is now "OK" but in my charts values are all =0

• ###### Re: max() function for charts

Can you add another expression to your chart

=Max(TOTAL <Activité> [date import]), ID))

and see what output it shows you?

• ###### Re: max() function for charts

I tried but I didn't manage to get any good result. I also added the .qvf file to my initial message. So it's maybe easier to do some trial...

• ###### Re: max() function for charts

Awesome, checking it now

• ###### Re: max() function for charts

See if this expression helps:

Count(If(Aggr([date import] = Max(TOTAL <[Activité]> [date import]), [Activité], MSN, [date import]), ID))

• ###### Re: max() function for charts

Perfect. I would never have found this on my own. Never had to do something that twisty. Thanks for your help and your time Sunny. I hope I can return the favour one day, but a lot of training needed before!

Have a nice day!

• ###### Re: max() function for charts

Hahaha it took me a while before I figured it myself, but I know people in the community who would have given you the solution in a blink of an eye. I am learning, you are learning, we all learning. So one day, I would be glad to receive your help.

Best,

Sunny

• ###### Re: max() function for charts

That was very fast Sunny. It worked perfectly! Many thanks !

• ###### Re: max() function for charts

Awesome

I am glad I was able to help you quickly.

Best,

Sunny