Hi, trying to create this aggregated table (image below) with quicksight.
----------Look at the image i have added below for raw data and desired output---------
I have a data set (on image) where per raw you can see per job_id (with the job skills) and assignment_date the number of assignments on that day for that job id (called ASSIGNMENTS) and the total number of assignments for all jobs at the day (called TOTAL_ASSIGNMENTS).
I want show on a table chart in quicksight (on image) per skill, the total assignments of all jobs with that skill (ASSIGNMENTS) divide to the total assignments (TOTAL_ASSIGNMENTS) of all jobs in that specific period.
NOTE— i need an assignment_date filter so you can always see the data relevant based on the desired assignment date.
For example (below) if i filter on 2021-07-09 then every skill will be divided by the same number - in this case 35.
But if i don’t use the filter and show all data then every skill will be divided by the same number - in this case 86.
I don’t have a problem creating the Sum of assignments but the second part of the equitation (TOTAL_ASSIGNMENTS)
I have tried using:
avg({TOTAL_ASSIGNMENTS},[{ASSIGNMENT_DATE},JOB_ID}])
And it works only with the ASSIGNMENT_DATE as group by but once switching to looking at SKILL_NAME level it wont do it.
Tried to do it as well with “sumOver” but it won’t present data because the ASSIGNMENT_DATE is not part of the chart (But id do need it as filter!)
There will be more then 2 job_id’s in the real dataset and more then 2 dates !!!
----------Look at the image i have added below for raw data and desired output---------
Thank you for sharing the additional details. The calculations will dynamically adjust based on the dataset, irrespective of the number of Job IDs. The aggregated functions are designed to handle larger datasets and provide accurate results.
Thank you @Xclipse but it’s not working. when looking yearly It will sum the job with the lowest amount total assignments + he job with the highest amount total assignments.
I need the sum of total_assignments for the all year when each day represent the total_assignments for all jobs in the same day.
I saw you are on quicksight team. wonder if we can set 30 min zoom and i could show you what i mean? we are big customer of quicksight.
Use Level-Aware Aggregations (LAC) in QuickSight, SumOver to calculate totals accurately across specific dimensions like dates or years. LAC ensures precise control over aggregation levels for complex scenarios.
I presume TOTAL_ASSIGNMENTS for all the rows in your raw data for a single assignment day will be the same value. If that is the case may be this is what you are looking for
Thank you @Giridhar.Prabhu but it’s not working as well. @Xclipse The reason i’m looking for a solution in the first hand is because there is no simple option to show distribution (100%) of all values like the simple option they have in Tableau.
Why is it? you can sum,count but not show them as % of all values on graph (beside on pie chart).
Hi @Saarben,
It’s been awhile since we last heard from you, were you able to find a work around for your case or did you have any additional questions?
If we do not hear back within the next 3 business days, I’ll close out this topic.
Hi @Saarben,
Since we have not heard back, I’ll go ahead and close out this topic. However, if you have any additional questions, feel free to create a new post in the community and link this discussion for relevant information if needed.
