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: 
chinnu123
Creator
Creator

Need to get data every month 1st to month end dynamically.

Hi Experts,

I am generating one report in qlik there my requirement is need to get data from Every month starting to ending dynamically

WHERE end_date IS NOT NULL AND (TRUNC(end_date) BETWEEN TO_DATE('2016-10-01 00:00:00','yyyy-mm-dd hh24:mi:ss') AND

                                   TO_DATE('2016-10-31 00:00:00','yyyy-mm-dd hh24:mi:ss')))

        

The above condition are using in cognos for generating montly reports but they are Manually entered so I need this in qlik to generate every month dynamicall i.e Month Ist to Month end.

Can any one help on this please..

Thanks in advance,

Chinnu.

7 Replies
sunny_talwar

Month Start for today's month?

MonthStart(Today()) and MonthEnd(Today())

Gysbert_Wassenaar

Perhaps like this:

LET vMonthStart = num(MonthStart(Today()));

LET vMonthEnd = num(MonthEnd(Today()));


YourQlikviewTable:
SELECT

     *

FROM

     YourSQLDatabase.YourTable

WHERE

     end_date BETWEEN $(vMonthStart) AND $(vMonthEnd)    



talk is cheap, supply exceeds demand
chinnu123
Creator
Creator
Author

Hi Gysbert,

Thanks for your reply

Unfortunately that condition is not working

Thanks,

Chinnu,

chinnu123
Creator
Creator
Author

Hi Sunny,

when I am writing this in where condition its not working.

Thanks,

Chinnu.

Kushal_Chawda

LET vMonthStart = floor(MonthStart(Today()));

LET vMonthEnd = floor(MonthEnd(Today()));


SQL SELECT *

FROM Schema.TableName

WHERE end_date IS NOT NULL AND TRUNC(end_date) BETWEEN '$(vMonthStart)' AND '$(vMonthEnd)'   ;

chinnu123
Creator
Creator
Author

Hi Kushal,

Its not working..

Gysbert_Wassenaar

Perhaps like this then :

LET vMonthStart = TimeStamp(MonthStart(Today()),'YYYY-MM-DD 00:00:00');

LET vMonthEnd = Date(MonthEnd(Today()),'YYYY-MM-DD 00:00:00');


YourQlikviewTable:
SELECT

     *

FROM

     YourSQLDatabase.YourTable

WHERE

     end_date BETWEEN TO_DATE('$(vMonthStart)','yyyy-mm-dd hh24:mi:ss')

                      AND TO_DATE('$(vMonthEnd)','yyyy-mm-dd hh24:mi:ss')))


talk is cheap, supply exceeds demand