Hello the community,
I need to calculate the annual average of Pages Views , and i do not manage to use the average function.
This is the dataset (cf pj):
| Marque | @CLE_Date | 2018-01 | 2018-02 | 2018-03 | 2018-04 | 2018-05 | 2018-06 | 2018-08 | 2018-09 | 2018-10 | 2018-11 | 2018-12 | 2019-01 | 2019-02 |
| marque1 | | 262,9 | 253,2 | 261,4 | 243,6 | 240,8 | 239,8 | 254,6 | 243,8 | 268,6 | 282,1 | 254,8 | 280,8 | 256,9 |
| marque2 | | 139,6 | 135,3 | 143,7 | 129,8 | 143,8 | 143,1 | 120,4 | 137,1 | 145,8 | 135,9 | 145,0 | 136,3 | 135,4 |
| marque3 | | 58,9 | 54,1 | 58,5 | 54,7 | 52,1 | 52,6 | 58,5 | 49,2 | 55,4 | 55,4 | 55,7 | 59,6 | 48,3 |
------------------
Dimensions =
Marque = marque1, marque2, marque3
@CLE_Date = YYYY-MM
Expressions = PV=
sum({<Indicateur = {'Pages Vues'}>} Value)/1000000
------------------------------
I manage to get the average dividing the sum by the number of months :
(sum({<Indicateur = {'Pages Vues'}, Année >} Valeur)/1000000)/
(Count (DISTINCT @CLE_Date))
But i would like to understand why it does not work with the average function.
for ex, this expression does not work :
(avg({<Indicateur = {'Pages Vues'}, Année >} Valeur)/1000000)
---------------------------------------------------------------------
I also need to calculate the cumul regarding the selected month.
For instance, if i select Feb 2019
| cumul Feb2018 | cumul Feb2019 | variation |
| 516,1 | 537,7 | 4% |
| 274,9 | 271,7 | -1% |
| 113,0 | 107,9 | -4% |
How could i do this, with this calendar :
Calendrier:
LOAD distinct
YEAR($(Var.Date.Min)+RecNo())&'-'&NUM(Month($(Var.Date.Min)+RecNo()),'00') as [@CLE_Date],
YEAR($(Var.Date.Min)+RecNo())&'-'&NUM(Month($(Var.Date.Min)+RecNo()),'00') as Année.Mois,
MonthName($(Var.Date.Min)+RecNo()) as [AnnéeMois Nom],
YEAR($(Var.Date.Min)+RecNo()) as [Année],
Month($(Var.Date.Min)+RecNo()) as [Mois Nom],
NUM(Month($(Var.Date.Min)+RecNo()),'00') as [Mois],
Date($(Var.Date.Min)+RecNo()) as Date
AUTOGENERATE($(Var.Nb.Days));
Finally, for each value, i would like to put a conditionned color when values are superior to the average.
cf sheet "needs".
Is it possible to build a kind of heatmap
> green if is superior of +10%
> yellow if it is superior from 5% to 9,9%....
I hope this is clear.
Thank you very much for your help !
