topic Re: Monthly chart in QlikView

Monthly chart

soniasweety — Wed, 25 Nov 2020 16:16:04 GMT

hi all

I want to show the last 12 months data in bar chart

so I have date fields in the script like below

Year -2018

Month -1,2,3,4,5....

week -19,20...

so my chart should show result like below same Month year format and measure is avg(secs)

Re: Monthly chart

YoussefBelloum — Tue, 05 Jun 2018 08:24:53 GMT

Hi,

take a look at this:

Calculating rolling n-period totals, averages or other aggregations

Re: Monthly chart

niclaz79 — Tue, 05 Jun 2018 08:55:44 GMT

Hi,

Usually in my calendar I always create 'MonthIndex' field that is a number generated from 1 and upwards on all month, which I then can reference in my set expression. It makes it easier especially when you cross over years etc. It's extremely useful in most applications.

So use

AutoNumber(Year, Month) as MonthIndex

in your script.

And then in the expression you can use:

Avg({<Year =, Month =, MonthIndex = {"<=$(=Max(MonthIndex)) > $(Max(MonthIndex)-12)"}>}Secs)

Re: Monthly chart

jyothish8807 — Tue, 05 Jun 2018 09:43:16 GMT

Hi Song,

You can try like this also:

Create a inline table like this:

map:

Load * inline [

Month,	MonthName
1	,Jan
2,	Feb
3,	Mar
4	,Apr
5,	May
6	,Jun
7,	Jul
8	,Aug
9,	Sep
10	,Oct
11,	Nov
12,	Dec

];

join

YourTable:

load*

from <>;

No concatenate

T3:

Load

date(Date#(Year &' '& Monthname,'YYYY MMM') ,'MMM-YYYY') as NewDate

from <>

drop table map;

*************************************************************

Then in UI:

Dimension: NewDate

Expression: Avg({<Year =, Month =, NewDate= {"<=$(=date(Max(NewDate),'MMM-YYYY')) > $(=date(addmonths(Max(NewDate),-12),'MMM-YYYY'))"}>}Secs)

Br,