Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Try this for you name in the calculated dimension?
= IF(Aggr(Max(VALUE), NAME), NAME)
That didn't seem to change anything, Nagaraju. I am still seeing CER across the entire bar chart.
Hi John,
maybe
FirstSortedValue(NAME,-Aggr(Sum(VALUE),DATE,HOUR))
Regards,
Antonio
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)
)
IS this what you are looking for?
No. Again, CER is being displayed, not the correct NAME per highest VALUE by HOUR.
That just displays CER.
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.