Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

kakaderanjit53
Contributor III

Order By Month

Hi Community,

I have to sort out the data by month wise,

for same purpose I written a script-code as below:

//compute the month-wise brokerage

Brokerage_QLIK:

LOAD Left(trade_date,6) as fiscal_month,

sum(brokerage) as Total_Brokerage

Resident Brokerage

Group by  Left(trade_date,6)

Order by 1;

But My Output data in Qliksense is not appearing as per required OrderSmiley TongueFA more details and help me please

1 Solution

Accepted Solutions
MVP
MVP

Re: Order By Month

Does this help?

Brokerage_QLIK_TEMP:

LOAD Date(Date#(Left(trade_date, 6), 'YYYYMM'), 'YYYYMM') as fiscal_month1,

     brokerage

Resident Brokerage;


Brokerage_QLIK:

LOAD fiscal_month1 as fiscal_month,

     sum(brokerage) as Total_Brokerage

Resident Brokerage_QLIK_TEMP

Group by fiscal_month1

Order By fiscal_month1;


DROP Table Brokerage_QLIK_TEMP;

9 Replies
kakaderanjit53
Contributor III

Re: Order By Month

I have to present the monthdata by ascending order

MVP
MVP

Re: Order By Month

How about you try this:

Brokerage_QLIK:

LOAD fiscal_month,

     sum(brokerage) as Total_Brokerage

Group by fiscal_month;

LOAD Date(Date#(Left(trade_date, 6), 'YYYYMM'), 'YYYYMM') as fiscal_month,

     brokerage

Resident Brokerage;

kakaderanjit53
Contributor III

Re: Order By Month

As per your script,No changes in the data presentation

MVP
MVP

Re: Order By Month

Did you sort by fiscal_month in your chart?

kakaderanjit53
Contributor III

Re: Order By Month

No, using this script for data reconcillation purpose in Log file of qliksense, I have checked it by data reloading at the data model viewer using table preview mode

kakaderanjit53
Contributor III

Re: Order By Month

My whole script for the same for Qlik_data



$(Must_Include='lib://***************\DataPath\PathFile.txt');

2

LET vQVD_Path = '$(vQvd_path_ODS_Transactions)';

3

LET vQVD_Name = 'PLFMWISE_BRKG_TURNOVR';

4

5

//ENTER EITHER vNoOfYears OR vFrom_Fiscal_Yr;

6

LET vNoOfYears = 1;   //INPUT SHOULD BE > 0 eg: 3 TO GET LAST 3 FISCAL YRS + 1 CURRENT FISCAL YR

7

LET vFrom_Fiscal_Yr = 'NONE'; // INPUT FORMAT eg: 'FY 2013' GETS DATA FROM FISCAL YR 2013 TO TILL NOW[2017 FISCAL YR]

8

LET vTable_Name = 'Brokerage';

9

10

$(Must_Include='*************************.qvs');

11

12

//brokerage summary for min date, max date & total brokerage

13

BrokerageSummary: 

14

Load 

15

Min(trade_date) as Min_Date,

16

Max(trade_date) as Max_Date,

17

Sum(brokerage) as Total_Brokerage

18

Resident Brokerage;

19

20

21

//store the summary data into variables

22

LET vFrom_Date = Peek('Min_Date',0,'BrokerageSummary');

23

LET vTo_Date = Peek('Max_Date',0,'BrokerageSummary');

24

Let vTotal_Brokerage= Peek('Total_Brokerage',0,'BrokerageSummary');

25

26

//drop the summary table

27

drop table BrokerageSummary;

28

29

30

31

Brokerage_QLIK:

32

LOAD fiscal_month,

33

   sum(brokerage) as Total_Brokerage

34

Group by fiscal_month;

35

LOAD Date(Date#(Left(trade_date, 6), 'YYYYMM'), 'YYYYMM') as fiscal_month,

36

   brokerage

37

Resident Brokerage;

38

39

40

//drop unwanted tables

41

drop table Brokerage;
MVP
MVP

Re: Order By Month

Does this help?

Brokerage_QLIK_TEMP:

LOAD Date(Date#(Left(trade_date, 6), 'YYYYMM'), 'YYYYMM') as fiscal_month1,

     brokerage

Resident Brokerage;


Brokerage_QLIK:

LOAD fiscal_month1 as fiscal_month,

     sum(brokerage) as Total_Brokerage

Resident Brokerage_QLIK_TEMP

Group by fiscal_month1

Order By fiscal_month1;


DROP Table Brokerage_QLIK_TEMP;

kakaderanjit53
Contributor III

Re: Order By Month

Hi Sunny,

Thanks alot.,

your script given me correct result.

MVP
MVP

Re: Order By Month

Super

Community Browser