Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Month Start for today's month?
MonthStart(Today()) and MonthEnd(Today())
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)
Hi Gysbert,
Thanks for your reply
Unfortunately that condition is not working
Thanks,
Chinnu,
Hi Sunny,
when I am writing this in where condition its not working.
Thanks,
Chinnu.
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)' ;
Hi Kushal,
Its not working..
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')))