Skip to main content
Qlik Connect 2024! Seize endless possibilities! LEARN MORE
Showing results for 
Search instead for 
Did you mean: 

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):

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


How could i do this, with this  calendar : 


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



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 !


0 Replies