Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Qlik Analytics Tour 2020 Online. Begins August 10th. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator
Creator

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 !