Showing a field depending on another field

[MagnusRydberg1]

I have a file that contains the number of users per day in my system. I want to display just the date that has the maximum value of users. In the example below i want to display the date 2023-11-06 as the date where the maximum number of users 589 occured.

How do I write this as a function in a KPI-box 

My file looks like this: 

Date	NumberOfUsers
2023-11-01	525
2023-11-02	535
2023-11-03	545
2023-11-04	495
2023-11-05	526
2023-11-06	624
2023-11-07	547
2023-11-08	498

[jochem_zw]

You could try this:

Only({<NumberOfUsers={$(=Max(Total NumberOfUsers))}>}Date)

[MagnusRydberg1]

Hi Jochem!

Thanks for your help but I didn't succed.

I tried with this as a function in my KPI -box: 

Only({<SumUserLogindate={$(=Max(SumUserLogindate))}>}SumULoginDate)

My Script that generates the data look lik this:

// User Statistics
EnvUsersLog1:
LOAD
LoginDate as ULoginDate,
Year(LoginDate) as UYear,
Week(LoginDate) as UWeek,
dual( Year(LoginDate) & '-' & Week(LoginDate), num(Year(LoginDate)) & num(Week(LoginDate), '00' )) as UYearWeek,
If(1=1,'Portal') as UType,
dual( Year(LoginDate) & '-' & Month(LoginDate), num(Year(LoginDate)) & num(Month(LoginDate), '00' )) as UYearMonth,
Count(DISTINCT "UserId") as UUniqueUserPortal
where(num(weekday(LoginDate))<>6 and num(weekday(LoginDate))<>7) Group By LoginDate;
SQL SELECT *FROM cacore.EnvUsersLog;

Join
LOAD
LoginDate as ULoginDate,
Year(LoginDate) as UYear,
Week(LoginDate) as UWeek,
dual( Year(LoginDate) & '-' & Week(LoginDate), num(Year(LoginDate)) & num(Week(LoginDate), '00' )) as UYearWeek,
dual( Year(LoginDate) & '-' & Month(LoginDate), num(Year(LoginDate)) & num(Month(LoginDate), '00' )) as [UYearMonth],
If(1=1,'App') as UType,
SystemType as USystemTyp,
Count(DISTINCT "UserID") as UUniqueUserApp
where(num(weekday(LoginDate))<>6 and num(weekday(LoginDate))<>7) Group By LoginDate,SystemType;
SQL Select *FROM OTSDATA.UserLoginLog;

EnvUserMaxCount:
Load
ULoginDate as SumULoginDate,
Sum("UUniqueUserPortal")+ Sum("UUniqueUserApp") as SumUserLogindate
Resident EnvUsersLog1
Group By ULoginDate;

What am I doing wrong?

Thanks in advance for your help.

// Magnus

[mikaelsc]

how about a good old firstsortedvalue () ? 

https://help.qlik.com/en-US/qlikview/May2023/Subsystems/Client/Content/QV_QlikView/Scripting/Aggrega...

[jochem_zw]

This should be working, see screenshots

[MagnusRydberg1]

Thanks Mikaelsc but I can't get it to work with "firstsortedvalue".

I've tried this but I get no value in DateMaxUser

EnvUserMaxCount:
Load
ULoginDate as SumULoginDate,
Sum("UUniqueUserPortal")+ Sum("UUniqueUserApp") as SumUserLoginDate
Resident EnvUsersLog1
Group By ULoginDate;

LOAD FirstSortedValue(SumULoginDate,-SumUserLoginDate) as DateMaxUser
Resident EnvUserMaxCount Group By SumULoginDate;

I may have completely misunderstood the function"firstsortedvalue"?

Thanks for your answer.

// Magnus

[MagnusRydberg1]

Thanks again Jochem for trying to help me.

Sorry but I can't get it to work.

I have this function in my KPI-box

Only({<SumUserLoginDate={$(=Max(SumUserLoginDate))}>}SumULoginDate)

Maybe it doesn't work when my data  build  on resident values in my sqript:

EnvUserMaxCount:
Load
ULoginDate as SumULoginDate,
Sum("UUniqueUserPortal")+ Sum("UUniqueUserApp") as SumUserLoginDate
Resident EnvUsersLog1
Group By ULoginDate;

But the data looks OK when i show it in a table

See screenshot:

[jochem_zw · Accepted solution]

think you have multiple '624' values with different dates??

pleaase try in a table not as a kpi object:

=Concat({<NumberOfUsers={$(=Max(NumberOfUsers))}>}Date,', ')

[SindujaDevendran]

Can you try the below expression in KPI chart.

= Firstsortedvalue(NumberofUsers,  -Date)

[MagnusRydberg1]

Spot on Jochem! 😊

As you said there were 2 dates with the same value 624.

By using your last function =Concat({<NumberOfUsers={$(=Max(NumberOfUsers))}>}Date,', ') in my KPI-box it now shows the 2 dates (se my screen shot)

Many thanks to you Jochem for helping me in an excellent way! 

// Magnus