Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT
Highlighted
Not applicable

Averange from the last 12 months

I've got table looking like this:

ReadingDate,=avg(Cost)

,£189.56

11/04/2011,£10.00

28/05/2011,£326.00

02/06/2011,£12.00

28/06/2011,£53.00

10/09/2011,£956.00

11/10/2011,£63.00

01/01/2012,£36.00

11/04/2012,£150.00

12/05/2012,£100.00

and i am able to make averange of single date, but how to make averange of the year, but not from january to december, but from ex. from may last year to may this year, so from the last 12 months?

Tags (2)
1 Solution

Accepted Solutions
miikkaqlick
Contributor II

Re: Averange from the last 12 months

Hi!

First of all, Qlikview does have function MakeWeekDate. You have Year and Week, that's all you need.

Put these to your script:

MakeWeekDate(Year, Week, 0) as Date, //This gives you proper day. Monday for weekday.

If( IsNull(LineValue), 0, LineValue) as LineValue //This replaces NULL-values from data

Then

=Avg( {$<Date = {"<=$(=Max(Date))>=$(=AddMonths(Date,-6)) "} >} LineValue)

This should do the trick.

Br,

Miikka

Climber Finland

View solution in original post

11 Replies
Not applicable

Re: Averange from the last 12 months

Can you post an example qvw?

ramoncova06
Valued Contributor III

Re: Averange from the last 12 months

https://community.qlik.com/docs/DOC-4247

check the info in this link

Not applicable

Re: Averange from the last 12 months

You can use for example a calculate dimension like =Month(YourDateField) and then apply to that chart dimension some expressions, but certainly it isn't the most appropriate solution.


First of all, you have to use a/some calendar lookup table/s to solve this type of issues.

buzzy996
Honored Contributor II

Re: Averange from the last 12 months

try this,

=num(avg({$<Year = {$(=only(Year))}, Month = {"<=$(=max(Month))"}>} LineSalesAmount), '$#,##0;($#,##0)')

Not applicable

Re: Averange from the last 12 months

I'll describe it a bit more accurately. I've got date in format not compatible with Qlik (year & week number), but i already solved this and i wrote function which checks if date is in my chosen range or not. If it is, it writes sum of costs from that week, if not - it puts 'NaN' or 0 (none of that worked so far). So i've got table made of '-' (none value), 'NaN' (value not-interesting me at the moment) and actual numeric value. I'm very new to Qlik and i stuck at the moment of making avg of that column. And if you could tell me where i should write script to get one avg from the whole column, i'd be very glad.

Also thank you for trying to help me

buzzy996
Honored Contributor II

Re: Averange from the last 12 months

is't possible to share ur .qvw app?

Not applicable

Re: Averange from the last 12 months

Sorry, i can't do that, but i made screenshot explaining the situationavg.png

miikkaqlick
Contributor II

Re: Averange from the last 12 months

Hi!

First of all, Qlikview does have function MakeWeekDate. You have Year and Week, that's all you need.

Put these to your script:

MakeWeekDate(Year, Week, 0) as Date, //This gives you proper day. Monday for weekday.

If( IsNull(LineValue), 0, LineValue) as LineValue //This replaces NULL-values from data

Then

=Avg( {$<Date = {"<=$(=Max(Date))>=$(=AddMonths(Date,-6)) "} >} LineValue)

This should do the trick.

Br,

Miikka

Climber Finland

View solution in original post

buzzy996
Honored Contributor II

Re: Averange from the last 12 months

okie,try tis way..

=If(urfield='NaN','NaN',avg({$<Year = {$(=only(Year))}, Month = {"<=$(=max(Month))"}>} urfield)


note:hope u have month &year fields in ur table.