topic Fiscal year - Order Months in line chart in Visualization and Usability

Fiscal year - Order Months in line chart

Tue, 14 Apr 2015 08:02:50 GMT

Hi everyone,

I’m beginning with Qlik Sense and I would like to create a line chart to analyze the changes in cash over a year.

I have tried to create this simple chart using measuring sum (CASH) and size MONTH field.

The problem is that the financial year of my company does not coincide with the calendar year.

In the created graph, months are in chronological order ( January, February ... ), or I would like it to be in the following order :

July, August , September, October , November, December , January, February , March, April , May, June

Do you know how I can sort the month in that order ?

I tried to change SET FirstMonthOfYear = 1 in to SET FirstMonthOfYear = 7 but the result is wrong :

When I load my data I get this order :

July, August , September, October , November, December , June, January, February , March, April , May

Do you know why June is loaded after December ?

Thank you for your help

Sincerely,

Re: Fiscal year - Order Months in line chart

Tue, 14 Apr 2015 08:19:52 GMT

Hi Julien,

Use the below expression in the back end script.

If(Num(Month(CalDate))>6,Num(Month(CalDate))-6,Num(Month(CalDate))+9)

as [Fiscal Month Number]

add the Fiscal Month Number in sort tab and select option as Ascending order.

Regards,

Sridhar

Re: Fiscal year - Order Months in line chart

Tue, 14 Apr 2015 08:26:15 GMT

You can always order a text loockup, like MONTH in your case, by disabling the automatic sorting in the sorting options, and enabling the option ORDER BY EXPRESSION. Now you can create your custom expression that associates a sequential ID/number for every MONTH.

In your case the expression (to be ordered increasingly) would be this:

if(MONTH='July',1,

if(MONTH='August',2,

if(MONTH='September',3,

if(MONTH='October',4,

if(MONTH='November',5,

if(MONTH='December',6,

if(MONTH='January',7,

if(MONTH='February',8,

if(MONTH='March',9,

if(MONTH='April',10,

if(MONTH='May',11,

if(MONTH='June',12

))))))))))))

I hope that wolud be helpful. Mark as correct if it is.

Carlo.

Re: Fiscal year - Order Months in line chart

Tue, 14 Apr 2015 08:36:25 GMT

Clearly you can use the same expression in load script, defining the final result with the alias you prefer.

Like this:

LOAD

MONTH,

if(MONTH='July',1,

if(MONTH='August',2,

if(MONTH='September',3,

if(MONTH='October',4,

if(MONTH='November',5,

if(MONTH='December',6,

if(MONTH='January',7,

if(MONTH='February',8,

if(MONTH='March',9,

if(MONTH='April',10,

if(MONTH='May',11,

if(MONTH='June',12

)))))))))))) as FISCAL_MONTH_ID

after that, you can always order using this new field.

Re: Fiscal year - Order Months in line chart

Tue, 14 Apr 2015 09:13:20 GMT

Thank you for your help.

I'm sorry but i don't know how to insert this script in my script.

Here is my script. Do you mind helping me ?

SET ThousandSep=' ';
SET DecimalSep=',';
SET MoneyThousandSep=' ';
SET MoneyDecimalSep=',';
SET MoneyFormat='# ##0,00 €;-# ##0,00 €';
SET TimeFormat='hh:mm:ss';
SET DateFormat='DD/MM/YYYY';
SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff]';
SET MonthNames='janv.;févr.;mars;avr.;mai;juin;juil.;août;sept.;oct.;nov.;déc.';
SET DayNames='lun.;mar.;mer.;jeu.;ven.;sam.;dim.';
SET LongMonthNames='janvier;février;mars;avril;mai;juin;juillet;août;septembre;octobre;novembre;décembre';
SET LongDayNames='lundi;mardi;mercredi;jeudi;vendredi;samedi;dimanche';
SET FirstWeekDay=0;
SET BrokenWeeks=1;
SET ReferenceDay=0;
SET FirstMonthOfYear=1;
SET CollationLocale='fr-FR';

SET DebutTreso1 = 50700000;
SET FinTreso1 = 50879999;
SET DebutTreso2 = 50900000;
SET FinTreso2 = 51779999;
SET DebutTreso3 = 51900000;
SET FinTreso3 = 51979999;
SET DebutTreso4 = 51990000;
SET FinTreso4 = 53999999;

//Chargement des FEC et concaténation des fichiers
[FEC ORIGINAL]:
LOAD *
FROM [lib://TDB/FEC N.txt]
(txt, utf8, embedded labels, delimiter is '\t', msq);
LOAD *
FROM [lib://TDB/FEC N-1.txt]
(txt, utf8, embedded labels, delimiter is '\t', msq);
LOAD *
FROM [lib://TDB/FEC N-2.txt]
(txt, utf8, embedded labels, delimiter is '\t', msq);

//Sauvegarde du fichier concatené en qvd
Store [FEC ORIGINAL] into [lib://TDB/FEC ORIGINAL.qvd] (qvd);

//Retraitement du FEC
[FEC RETRAITE]:
LOAD
    JournalCode as [CODE JOURNAL],
    JournalLib as [LIBELLE JOURNAL],
    EcritureNum as [NUMERO ECRITURE],
    CompteNum as [NUMERO COMPTE],
    CompteLib as [LIBELLE COMPTE],
    CompAuxNum as [NUMERO COMPTE AUXILIAIRE],
    CompAuxLib as [LIBELLE COMPTE AUXILIAIRE],
    PieceRef as [REFERENCE PIECE],
    PieceDate as [DATE PIECE],
    EcritureLib as [LIBELLE ECRITURE],

If (Sens='C',Montant * -1,Montant) AS SOLDE,
Date(Date#([EcritureDate],'YYYYMMDD'),'DD/MM/YYYY') as [DATE ECRITURE],

If(CompteNum >=$(DebutTreso1) and CompteNum <=$(FinTreso1) or CompteNum >=$(DebutTreso2) and CompteNum <=$(FinTreso2) or CompteNum >=$(DebutTreso3) and CompteNum <=$(FinTreso3) or CompteNum >=$(DebutTreso4) and CompteNum <=$(FinTreso4),1,0) as FLAGTRESORERIE

FROM [lib://TDB/FEC ORIGINAL.qvd] (qvd);

CALENDRIER:
LOAD
    Date(Date#([EcritureDate],'YYYYMMDD'),'DD/MM/YYYY') as [DATE ECRITURE],
    Date(Date#([ValidDate],'YYYYMMDD'),'DD/MM/YYYY') as [EXERCICE],
    Date(Date#([EcritureDate],'YYYYMMDD'),'MM/YYYY') as [MOIS ANNEE],
    Month(Date#([EcritureDate],'YYYYMMDD')) as MOIS,
Year(Date#([EcritureDate],'YYYYMMDD')) as ANNEE

FROM [lib://TDB/FEC ORIGINAL.qvd] (qvd);

Sorry if this script is not very professionnel...and sorry for my English...i'm french

Thanks

Re: Fiscal year - Order Months in line chart

Tue, 14 Apr 2015 09:25:53 GMT

Julien,

Insert the expression in the CALENDRIER

If(Num(Month([EcritureDate]))>6,Num(Month([EcritureDate]))-6,Num(Month([EcritureDate]))+9)

as [Fiscal Month Number],

Year(Date#([EcritureDate],'YYYYMMDD')) as ANNEE

FROM [lib://TDB/FEC ORIGINAL.qvd] (qvd);

After adding the expression in CALENDRIER table reload the application.

Click on Edit and select you chart.

Go to Sort tab and add =[Fiscal Month Number] expression. select Ascending option.

Regards,

Sridhar

Re: Fiscal year - Order Months in line chart

Tue, 14 Apr 2015 09:36:57 GMT

I try to edit your calendier script using the powerful PRECEDING LOAD statement, wich perform concatenated load statements in a single "table declaration". For more datails you can check this document: Preceding Load

I edited the script directly from this blog, I hope there are no syntax errors:

CALENDRIER:

LOAD

if(Num(MOIS)=7,1,

if(Num(MOIS)=8,2,

if(Num(MOIS)=9,3,

if(Num(MOIS)=10,4,

if(Num(MOIS)=11,5,

if(Num(MOIS)=12,6,

if(Num(MOIS)=1,7,

if(Num(MOIS)=2,8,

if(Num(MOIS)=3,9,

if(Num(MOIS)=4,10,

if(Num(MOIS)=5,11,

if(Num(MOIS)=6,12

)))))))))))) as FISCAL_MOIS_ID;

LOAD
    Date(Date#([EcritureDate],'YYYYMMDD'),'DD/MM/YYYY') as [DATE ECRITURE],
    Date(Date#([ValidDate],'YYYYMMDD'),'DD/MM/YYYY') as [EXERCICE],
    Date(Date#([EcritureDate],'YYYYMMDD'),'MM/YYYY') as [MOIS ANNEE],
    Month(Date#([EcritureDate],'YYYYMMDD')) as MOIS,
Year(Date#([EcritureDate],'YYYYMMDD')) as ANNEE

FROM [lib://TDB/FEC ORIGINAL.qvd] (qvd);

Re: Fiscal year - Order Months in line chart

Tue, 14 Apr 2015 10:45:40 GMT

Tkank you,

It works.

I will take some time to read "precedent load" document.

Tkank you for this link.