Qlik Community

Ask a Question

App Development

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

Announcements
QlikWorld Online 2021, May 10-12: Our Free, Virtual, Global Event REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
mandilicious
Creator II
Creator II

Calendar

Date.PNG

 

Hi Champs

I am having a problem in creating calendar using below Query .In my dashboard date looks like above picture  so I need to separate day,month and year from above table? below query run smoothly .

 

SELECT distinct a.*, B.TRNS
FROM DBA.PRC_FACT A,
(
SELECT distinct PL_ID,
                CASE WHEN TRNS_CODE in (554,555,834,835,525,516,528,529) AND BULK_IND = 0 THEN 'CSD'
                     WHEN TRNS_CODE in (54,535,534,855,525,526,528,529) AND BULK_IND = 1 THEN 'CSDB'
                     WHEN TRNS_CODE in (101,152,155,156) AND BULK_IND = 0 THEN 'CQL'
                     WHEN TRNS_CODE in (450,632,934,936,938,940,942,944,946) AND BULK_IND = 0 THEN 'CIU'
                     WHEN TRNS_CODE in (741,722,776,776,703,794,711,71,711,722) AND BULK_IND = 0 THEN 'CBR'
                     WHEN TRNS_CODE in (721,742,777,78,703,794,111,112,721,712) AND BULK_IND = 1 THEN 'CSLK'
                     WHEN TRNS_CODE in (700,778,70,770,858,859) AND BULK_IND = 0 THEN 'CQDPADT'
                     WHEN TRNS_CODE in (702,73,779,780,795,796,711,714,713,724,127,728) AND BULK_IND = 0 THEN 'CQC'
                     ELSE 'N/A'
                END AS 'CHARGE_CODE',
                convert(int,sum(TRNS)) as 'TRNS'
FROM DBA.FACT_TRANSACTIONS f
GROUP BY PL_ID, CHARGE_CODE
)B
WHERE a.PROD_NAME = 'DA'
AND a.UCN = b.PL_ID
AND a.CHARGE_CODE = b.CHARGE_CODE

Thanking you in advance.

Labels (1)
1 Solution

Accepted Solutions
Gysbert_Wassenaar

Try putting this line directly above your sql statement:

LOAD *, Year(PERIOD) as YEAR, Month(PERIOD) as MONTH, Day(PERIOD) as DAY;

talk is cheap, supply exceeds demand

View solution in original post

7 Replies
Shubham_Deshmukh
Specialist
Specialist

What exactly you want, tell me sample output
mandilicious
Creator II
Creator II
Author

Date.PNG

 

Hi

Thanks for quick respond: I want to  separate day,month and year. Cause when I pull this into dashboard it look like above. I need to have month(1) and Day(1) separate, and Year(2019) separate to each other.I hope I make sense.

If  can have day 1 in one list box and Year as well.

 

Thanks

 

 

Shubham_Deshmukh
Specialist
Specialist

See, still m not getting your requirement. Write you expected output.
If you want month, day and year from any date then you can get it from
=day(date)
=Month(date)
=Year(date)
And you want to show this in list box?
Gysbert_Wassenaar

Try putting this line directly above your sql statement:

LOAD *, Year(PERIOD) as YEAR, Month(PERIOD) as MONTH, Day(PERIOD) as DAY;

talk is cheap, supply exceeds demand

View solution in original post

mandilicious
Creator II
Creator II
Author

Hi Gysbert

Thank you very much it worked perfect.

mandilicious
Creator II
Creator II
Author

Hi Gysbert

 

Perfect it is  working perfect Thanks

mandilicious
Creator II
Creator II
Author

Thanks for you assistant!