Hi all!
I have the following issue (?) in Quicksight and I was wondering if maybe some of you have come across something similar in the past and have a suggestion how to tackle it.
I am trying to create a KPI card (or a calc visual, just not a table) that would show shutdown ratio of the average amount of hours spent during business hours and after hours. This is how I would do that in Excel:
- Get the average of amount of hours for Business Hours (col. H)
- Get the average of amount of hours for After Hours (col. I)
- Calculate the ratio: 1-AVG(BusinessHours)/AVG(AfterHours) and show as % (col. J)
I found this article, but a) it includes table calculations (which I am not after as I would like to have a KPI card only), and b) I have another field based on which I would like to distinguish the ratio (Business/After hours) so it does not help much in the end. // Edit: cannot attach the link due to the embedded media items limit for the new users. Here is the title posted by Karthik1: “Create KPI field that shows hourly average of record count”
The closets I got to is to have the correct ratio %, but it appears as a negative value (Difference as %)
*Please note that the values in Excel and QS are different as in Excel I used dummy data.
My dataset has multiple fields, but for this calculation purpose I used only a few, so I started with the Shutdown Ratio LAC calc:
sum({usage_amount},[environment, domain, linkedacctname, {line_item_usage_start_date_Str}])
Then I created a KPI card and it works perfectly fine except the fact that it’s showing a negative value (in this case 32.65/43 = 76%, so the calculation shows -24% as a difference as % and I would like to see the absolute value/% which would be 24%)
BH/AH Calc is just a calculation to assign the string values “Business Hours” or “After Hours” to false/true values in the data.
// Apologies, I cannot post a screenshot of the KPI in this post due to the embedded media items limit for the new users. I replied in this thread with the screenshot. Anyway, I put the Shutdown ratio (aggregated to show Average) calc to Value and BH/AH Calc to Trend group. selected Difference as percent % option in Comparison method and got -24% as a result.
I hope this explanation it clear and you guys understand what I am after. Any ideas and suggestion will be much appreciated!
Thank you
Iwona