Hi All, Im a newbie to the community and any help from the community members would be appreciated
I am trying to create a chart that shows the percentage difference between 2 charts. here is something I have created in excel.
1 Chart containing the percent total of different categories by source for last week.
2 Chart containing the percent total of different categories by source for current week.
I would want to create a chart which will show me the difference between current week results and the last week results basically it is (current week results - last week results) something like below
How can I achieve this in Quicksight?
Hello @Kishan, welcome to the QuickSight Community!
The solution that I would recommend would be utilizing the lag function in a calculated field. I am suggesting lag because it will allow you to grab a field from a row linked to the previous week and add it as a new column value on the row linked to current week. This will allow you to aggregate it with the current week value.
I am assuming that the 2 fields you outlined were created by calculated fields. If that is the case, QuickSight may throw an aggregation error if you try to bring the aggregated percent of total from last week. If that is the case, bring in the value that you use to calculate percent of total and then try running the calculation on the last week value. Then you should be able to calculate the difference. I hope that helps!
Thank you very much for the reply @DylanM.
I tried to come up with a solution using Lag function but I wan unable to meet the requirement. However, I used the percentoftotal calculated field for last week results and another percentoftotal calculated field for current week results and finally created the third calculated field for current week - last week result and I was able to achieve my requirements. Anyways Thank you very much for taking time to reply .