Monthly chart

[soniasweety]

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)

[YoussefBelloum]

Hi,

take a look at this:

Calculating rolling n-period totals, averages or other aggregations

[niclaz79]

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)

[jyothish8807]

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,

KC