Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
johnca
Specialist
Specialist

Get Max Name and Value Hourly

So, I've been struggling with this trying to get it to work. I've gotten most of it but just can't get over the hump. I am not strong in calculated dimensions and suspect that is where it will be fixed.

I need to display the NAME and maximum value for a particular NAME hourly throughout the day. I have multiple NAME's with a VALUE and it may or may not be associated to an HOUR of any given day. The attachment I provided has a table with 3 days of data. Each bar shows the correct VALUE but not the correct NAME. For the first date of 7/1 and HOUR 00 I would expect to see the VALUE of 162034 (which it does) and the NAME of HOS (which it doesn't). For HOUR 01 I expect to see VALUE of 141720 (which it does) and again NAME of HOS (which it doesn't).


Also, if I select TYPE A or B I expect to see the associated max VALUE's and associated NAME's.


I also need the legend to display the NAME and associated color. Note that this chart is showing only one color but again, the calculated NAME dimension isn't right and all are showing 3469.


I am suspecting my issue is in my calculated dimension but I've tried every permutation I could think of with no success. I've attached my document. If the answer partially lies in the script just export and reload the table.


Regards,

John

8 Replies
vishsaggi
Champion III
Champion III

Try this for you name in the calculated dimension?

= IF(Aggr(Max(VALUE), NAME), NAME)

johnca
Specialist
Specialist
Author

That didn't seem to change anything, Nagaraju. I am still seeing CER across the entire bar chart.

antoniotiman
Master III
Master III

Hi John,

maybe

FirstSortedValue(NAME,-Aggr(Sum(VALUE),DATE,HOUR))

Regards,

Antonio

RSvebeck
Specialist
Specialist

On my phone so i cant view your app, but try this for the name of a max value:


Assuming your "Hour dimension" is called YourHourDim 🙂


=only(


if(aggr(Rank(sum(Value),Name,YourHourDim)=1,

  Name)


)

Svebeck Consulting AB
vishsaggi
Champion III
Champion III

IS this what you are looking for?

Capture.PNG

johnca
Specialist
Specialist
Author

No. Again, CER is being displayed, not the correct NAME per highest VALUE by HOUR.

johnca
Specialist
Specialist
Author

That just displays CER.

johnca
Specialist
Specialist
Author

No. I'm looking for that single NAME with the highest VALUE for each HOUR. Looking at the data it's going to be HOS in most, if not all cases. When selections are made to limit the NAME's I'd expect the highest VALUE/NAME to be displayed.