Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikWorld, June 24-25, 2020. Free virtual event for DI and DA gurus. Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Specialist III
Specialist III

Monthly chart

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)

mnhts.PNG

Tags (2)
3 Replies
Highlighted
Champion
Champion

Re: Monthly chart

Highlighted
Partner
Partner

Re: Monthly chart

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)

Highlighted
Master II
Master II

Re: Monthly chart

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

Best Regards,
KC