topic Re: Get Max Name and Value Hourly in QlikView

Get Max Name and Value Hourly

johnca — Fri, 14 Jul 2017 20:26:59 GMT

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

Re: Get Max Name and Value Hourly

vishsaggi — Fri, 14 Jul 2017 21:09:15 GMT

Try this for you name in the calculated dimension?

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

Re: Get Max Name and Value Hourly

johnca — Fri, 14 Jul 2017 21:40:36 GMT

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

Re: Get Max Name and Value Hourly

antoniotiman — Fri, 14 Jul 2017 21:53:08 GMT

Hi John,

maybe

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

Regards,

Antonio

Re: Get Max Name and Value Hourly

RSvebeck — Fri, 14 Jul 2017 21:53:43 GMT

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)

)

Re: Get Max Name and Value Hourly

vishsaggi — Fri, 14 Jul 2017 22:22:53 GMT

IS this what you are looking for?

Re: Get Max Name and Value Hourly

johnca — Mon, 17 Jul 2017 14:00:15 GMT

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

Re: Get Max Name and Value Hourly

johnca — Mon, 17 Jul 2017 14:03:45 GMT

That just displays CER.

Re: Get Max Name and Value Hourly

johnca — Mon, 17 Jul 2017 14:05:17 GMT

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.