Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

paulyeo11
Valued Contributor II

How to plot average sales by year ?

Hi All

I have chart 1 using below expression , working fine all the time :-

money(

sum({$<year = {">=$(=max(year),4)"}, month = {"<=$(=max({<year={$(=max(year))}>} month))"}>}$(ColumnDim51)/$(Columndim89)/1000)

, $(vMoneyFormatK))

May i know how to convert it so that it will display average sales by year.

Note that 2016 only have 4 month and 2015 have 12 month. How to automatic adjust ?

2015 should display 347/12= 28.9

2016 should display 104/4 = 26

Paul

1 Solution

Accepted Solutions

Re: How to plot average sales by year ?

May be this:

Money(

Sum({$<year = {">=$(=max(year),4)"}, month = {"<=$(=max({<year={$(=max(year))}>} month))"}>}$(ColumnDim51)/$(Columndim89)/1000)/Count(DISTINCT {<date = {"=Sum(sales) > 0"}>} month)

, $(vMoneyFormatK))

3 Replies
paulyeo11
Valued Contributor II

Re: How to plot average sales by year ?

My QV Doc

Re: How to plot average sales by year ?

May be this:

Money(

Sum({$<year = {">=$(=max(year),4)"}, month = {"<=$(=max({<year={$(=max(year))}>} month))"}>}$(ColumnDim51)/$(Columndim89)/1000)/Count(DISTINCT {<date = {"=Sum(sales) > 0"}>} month)

, $(vMoneyFormatK))

paulyeo11
Valued Contributor II

Re: How to plot average sales by year ?

Hi sunny

Wow the below expression very powerful :-

Count(DISTINCT {<date = {"=Sum(sales) > 0"}>} month)


Thank you.


Paul

Community Browser