topic Re: month order on graph in QlikView

month order on graph

Wed, 25 Nov 2020 16:16:04 GMT

Hi,

Please see attached. We need the graph to show results in month order on fiscal year (i.e. April through to March).

As you can see it's not showing in order irrespective of 'sort' option.

Can someone please help?

Chris

Re: month order on graph

jagan — Wed, 06 Apr 2016 07:58:46 GMT

Hi Chris,

Create a master calendar and sort the way you want. Check for sample script in below link

Fiscal and Standard Calendar generation

Regards,

Jagan.

Re: month order on graph

Wed, 06 Apr 2016 08:03:48 GMT

hi please review this link.

Fiscal Year

regards Nishit

Re: month order on graph

Anonymous — Wed, 06 Apr 2016 08:24:52 GMT

Hi,

Create a master calendar is good idea, but you can also use

pick(Match(Month,'January','February','March','April','May','June','July','August','September','October','November','December'),1,2,3,4,5,6,7,8,9,10,11,12)

to sort your graphic by Month without reload and recreate table in the script.

Re: month order on graph

Wed, 06 Apr 2016 08:28:52 GMT

Hi,

Thanks for this but still isn't clear to us.

Do we literally copy and paste the below into our script? We don't understand what the three steps mean:

Set vFM = 4 ; // First month of fiscal year

Calendar:

Load Dual(fYear-1 &'/'& fYear, fYear) as FYear, // Dual fiscal year

Dual(Month, fMonth) as FMonth, // Dual fiscal month

Load Year + If(Month>=$(vFM), 1, 0) as fYear, // Numeric fiscal year

Mod(Month-$(vFM), 12)+1 as fMonth, // Numeric fiscal month

Load Year(Date) as Year, // Your standard master calendar

Month(Date) as Month,

Re: month order on graph

perumal_41 — Wed, 06 Apr 2016 08:45:53 GMT

Hi Chris,

Set vFM = 4 ; // First month of fiscal year

Calendar:

this place applying dual function

Load Dual(fYear-1 &'/'& fYear, fYear) as FYear, // Dual fiscal year

Dual(Month, fMonth) as FMonth, // Dual fiscal month

Below script preceding load of previous load script and changing the starting month based in set Variable input values on vFM i.e vFM = 4

Load Year + If(Month>=$(vFM), 1, 0) as fYear, // Numeric fiscal year

Mod(Month-$(vFM), 12)+1 as fMonth, // Numeric fiscal month

Below load statement your calendar script, and this place starting month will be jan

Load Year(Date) as Year, // Your standard master calendar

Month(Date) as Month,

Re: month order on graph

Wed, 06 Apr 2016 08:50:18 GMT

Hi Perumal,

We have a field called 'date of request' which is what were interested in. We need the script above (and referred to in other links) to then populate fical year and month from this field.

What do we have to change in script or load separately for this to work?

Chris

Re: month order on graph

perumal_41 — Wed, 06 Apr 2016 09:00:12 GMT

Hi chirs,

do you have master calendar in your script ?,

if yes then apply on top of master calendar .

else use below script

Load Dual(fYear-1 &'/'& fYear, fYear) as FYear, // Dual fiscal year

Dual(Month, fMonth) as FMonth, // Dual fiscal month

Load Year + If(Month>=$(vFM), 1, 0) as fYear, // Numeric fiscal year

Mod(Month-$(vFM), 12)+1 as fMonth, // Numeric fiscal month

LOAD

Date(Date) AS [date of request],

Year(Date) AS Year,

'Q' & Ceil(Month(Date) / 3) AS Quarter,

Month(Date) As Month,

Day(Date) As Day,

Week(Date) As Week;

Load Date(MinDate + IterNo() -1 ) AS Date While (MinDate + IterNo() - 1) <= Num(MaxDate);

Load

Min([date of request]) AS MinDate,

Max([date of request]) AS MaxDate

RESIDENT table;

Re: month order on graph

Wed, 06 Apr 2016 09:05:18 GMT

Hi,

Great. No we don't have a master calender in our script. How do we do this?

Chris

Re: month order on graph

perumal_41 — Wed, 06 Apr 2016 09:14:56 GMT

Try like below

Load Dual(fYear-1 &'/'& fYear, fYear) as FYear, // Dual fiscal year

Dual(Month, fMonth) as FMonth, // Dual fiscal month

Load Year + If(Month>=$(vFM), 1, 0) as fYear, // Numeric fiscal year

Mod(Month-$(vFM), 12)+1 as fMonth, // Numeric fiscal month

LOAD

Date(Date) AS [date of request],

Year(Date) AS Year,

'Q' & Ceil(Month(Date) / 3) AS Quarter,

Month(Date) As Month,

Day(Date) As Day,

Week(Date) As Week;

Load Date(MinDate + IterNo() -1 ) AS Date While (MinDate + IterNo() - 1) <= Num(MaxDate);

Load

Min([date of request]) AS MinDate,

Max([date of request]) AS MaxDate

RESIDENT factable ( date fileds contains table name) ;

Re: month order on graph

Wed, 06 Apr 2016 09:21:39 GMT

Hi,

Put this in script but it doesn't like MinDate and MaxDate and can you clarify 'RESIDENT factable'?

Set vFM = 4 ; // First month of fiscal year

Calendar:

Load Dual(fYear-1 &'/'& fYear, fYear) as FYear, // Dual fiscal year

Dual(Month, fMonth) as FMonth, // Dual fiscal month

Load Year + If(Month>=$(vFM), 1, 0) as fYear, // Numeric fiscal year

Mod(Month-$(vFM), 12)+1 as fMonth, // Numeric fiscal month

LOAD

Date(Date) AS [Date of request],

Year(Date) AS Year,

'Q' & Ceil(Month(Date) / 3) AS Quarter,

Month(Date) As Month,

Day(Date) As Day,

Week(Date) As Week;

Load Date(MinDate + IterNo() -1 ) AS Date While (MinDate + IterNo() - 1) <= Num(MaxDate);

Load

Min([Date of request]) AS MinDate,

Max([Date of request]) AS MaxDate

RESIDENT factable;

Re: month order on graph

Wed, 06 Apr 2016 09:25:40 GMT

Hi Fray

Just Add below script -

LOAD * INLINE [

Month, SEQ

Jan, 1

Feb, 2

March, 3

April, 4

May, 5

Jun, 6

Jul, 7

Aug, 8

Sep, 9

Oct, 10

Nov, 11

Dec, 12

];

Please update Month values as you have in your excel.

In graph at sort tab assign SEQ

It will resolve your problem simple way.

Regards,

Lokesh

Re: month order on graph

perumal_41 — Wed, 06 Apr 2016 09:26:19 GMT

which table contains [Date of request] fields

Re: month order on graph

Wed, 06 Apr 2016 09:48:38 GMT

Its in the main spreadsheet we are loading all our data in (this spreadsheet contains about 15 other columns of text).

Re: month order on graph

perumal_41 — Wed, 06 Apr 2016 09:55:26 GMT

is ok.

your loading record from excel file like below and On top assign table name like Sales

Sales :

LOAD *
FROM
D:\Test.xlsx
(ooxml, embedded labels, table is master_table);

that table name taking in the resident

Set vFM = 4 ; // First month of fiscal year

Calendar:

Load Dual(fYear-1 &'/'& fYear, fYear) as FYear, // Dual fiscal year

Dual(Month, fMonth) as FMonth, // Dual fiscal month

Load Year + If(Month>=$(vFM), 1, 0) as fYear, // Numeric fiscal year

Mod(Month-$(vFM), 12)+1 as fMonth, // Numeric fiscal month

LOAD

Date(Date) AS [Date of request],

Year(Date) AS Year,

'Q' & Ceil(Month(Date) / 3) AS Quarter,

Month(Date) As Month,

Day(Date) As Day,

Week(Date) As Week;

Load Date(MinDate + IterNo() -1 ) AS Date While (MinDate + IterNo() - 1) <= Num(MaxDate);

Load

Min([Date of request]) AS MinDate,

Max([Date of request]) AS MaxDate

RESIDENT sales;

Re: month order on graph

Wed, 06 Apr 2016 11:21:47 GMT

This loads; however, chart still isn't correct.

I have changed dimension to FMonth (which generates from your script); however, it then only shows one value:

The 901.10 is the sum of time spent; however, its only recognising the year, not the months on the x axis.

Re: month order on graph

perumal_41 — Wed, 06 Apr 2016 12:16:39 GMT

is possible to upload sample application?

Re: month order on graph

Wed, 06 Apr 2016 13:03:12 GMT

see atatched

Re: month order on graph

Wed, 06 Apr 2016 13:23:11 GMT

Change your dimension to =MonthName(Date([Month-Year],'DD/MM/YYYY')) in add Calculated dimension.

Re: month order on graph

perumal_41 — Wed, 06 Apr 2016 13:45:57 GMT

Hi Chris,

creating Synthetic Keys so only getting single record. comment Month and Year field in Timespent table