Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Fiscal year - Order Months in line chart

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,

 

1 Solution

Accepted Solutions
Not applicable
Author

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);

View solution in original post

7 Replies
Not applicable
Author

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

Not applicable
Author

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.

Not applicable
Author

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.

Not applicable
Author

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

Not applicable
Author

Julien,

Insert the expression in the CALENDRIER

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,

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

Not applicable
Author

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);

Not applicable
Author

Tkank you,

It works.

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

Tkank you for this link.