Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

johncaqc
Valued Contributor

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
Esteemed Contributor III

Re: Get Max Name and Value Hourly

Try this for you name in the calculated dimension?

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

johncaqc
Valued Contributor

Re: Get Max Name and Value Hourly

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

antoniotiman
Honored Contributor III

Re: Get Max Name and Value Hourly

Hi John,

maybe

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

Regards,

Antonio

rsvebeck
Valued Contributor

Re: Get Max Name and Value Hourly

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)


)

vishsaggi
Esteemed Contributor III

Re: Get Max Name and Value Hourly

IS this what you are looking for?

Capture.PNG

johncaqc
Valued Contributor

Re: Get Max Name and Value Hourly

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

johncaqc
Valued Contributor

Re: Get Max Name and Value Hourly

That just displays CER.

johncaqc
Valued Contributor

Re: Get Max Name and Value Hourly

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.

Community Browser