Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

marieclaire
Contributor

Qlikview : how to get average and cumul in a TCD

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_Date2018-012018-022018-032018-042018-052018-062018-082018-092018-102018-112018-122019-012019-02
marque1 262,9253,2261,4243,6240,8239,8254,6243,8268,6282,1254,8280,8256,9
marque2 139,6135,3143,7129,8143,8143,1120,4137,1145,8135,9145,0136,3135,4
marque3 58,954,158,554,752,152,658,549,255,455,455,759,648,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 Feb2018cumul Feb2019variation
516,1537,74%
274,9271,7-1%
113,0107,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 !