Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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)
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)
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