Hi all,
I'm trying to compare the selected data to the data before the selection.
I have a column that contains the games that each person plays.
player 1| Fortnite, League of Legend, Dota2
player 2| Fortnite, Dota2
player 3| Dota2
.
.
and I used SubField([Game Title], ',') AS GameUpdated to separate the games so that I can use the Count function.
Then I have a pivot table which shows the statistics data of the games based on the Count function in the descending order.
Basically it looks like this:
| GameTitle | Count |
|---|
| Dota2 | 3 |
| Fortnite | 2 |
| League of Legend | 1 |
The data in the pivot table is base on weeks. I can select the week and check the condition of that specific week.
What I'm having trouble now is I want to compare the count of the specific week to the data before the selection to see which game appears more compared to the avg (of all weeks).
e.g. If the data is from week 1 to 10 and I select week 9, I want to check which game in week 9 that has higher count than the average (the average includes week 9) and mark it with a different color.
so if the average count of Dota2 is 10 from week 1 to 10 and for week 9 it shows 13, it'll be marked with different color.
The expression I have now is If(avg(aggr(count({1} GameUpdated), GameUpdated))< count(GameUpdated), LightRed(100)), but it doesn't work as what I want.
Any help is appreciated.
