Hi, I have a QuickSight dashboard that shows a weekly sum of customer host counts by type and number of running VMs by company (see example). I need advice on how to create an insight visual showing the top 15 Year to date movers by host_count. I have it working for week over week movers, but not comparing from the beginning of the year to today. The data for each week should not be summed , rather I want compare the host_count value from 1/7/23 to the most recent week.
Any help is appreciated! Thanks
Hello @boatnerb - Thank you for posting your query. Will it be possible for you to share a snapshot of your dataset that you are using with the relevant columns. That would allow to create a mockup dataset at my end and I can give it a shot at this problem from my end. Thank you!
1 Like
Here’s an example of the data I’m working with . The data is fully refreshed weekly for all companies and I’m summing host_count, i3_host_count, i3en_host_count, i4i_host_count and number_running_vm at the company_name (col C) level to get their total for the week. Any given company_name may have more than or Org (col A) or SDDC (col B) so those would get summarized in the total for the week as well. The data goes back years for many companies, but some will come online throughout the year. I’d love to get the bigger movers from the start or the year. Another approach would be to look at the bigger movers over the last XX (4) weeks.
Appreciate the help!
@boatnerb - Thank you for providing the additional details. Based on my understanding of your problem statement, I have tried out one solution approach at my end using my sample dataset. Trying to share the same with you :
Step 1 : Since you are basically looking to find the overall delta between the first date and latest available date (rather than addition of all the values of the intermediate dates), we would go about creating 2 calculated fields to get that details.
Step 2 : Create a Calculated Field having the following kind of expression which would track the daily deltas i.e. changes between the dates. Expression that I used in my use case :
periodOverPeriodDifference(sum(Quantity),{Order Date}, DAY, 1)
This could be something like below in your case :
periodOverPeriodDifference(sum(host_count),{cal_weekenddate}, WEEK, 1)
Step 3 : Create another Calculated field to basically calculate the difference between your latest value minus initial value of the year. I used the following expression which basically uses the previous calculated field that I created as input. You can modify it accordingly to create according to your use case.
lastValue(runningSum(DailyDelta, [{Order Date} ASC]), [{Order Date} ASC])
Please see the final snapshot showing the values :
Step 4 : Finally you can create a (Dense) Rank field based on the previously calculated field to assign numbers from 1 to 15 at the report data level and utilize that in filter to only represent the first 15 companies that qualifies this criteria.
Hope this helps!
2 Likes
Thank you, @sagmukhe. I have the DailyDelta calculated field working, very helpful. But, I’m still struggling with the YTD/Total Delta one as it’s returning some very strange results based on my search / filters. I’ll keep experimenting. I really appreciate the suggestions.
1 Like
@sagmukhe I finally got the TotalDelta field working using this formula.
runningSum({WoW Host Count Delta}, [{cal_weekenddate} ASC],[{company_name}])
This looks at the entire data set (however you have filtered) and shows a the proper value (growth) from the first date to the last. Two issues: 1/when exported from a pivot visual, the value doesn’t match what was displayed. 2/I would like to compute growth from a designed “hard coded” date in the formula so I could have a calculated field such as 2023TotalHostDelta. I haven’t found a way to hard code a date in my formula, yet.
Appreciate any ideas.
Thanks
