Need to get date dynamically

[bharatkishore]

Dear Member i have the following code,

CLSCURRENT_MONTH_DB1:

LOAD *;

SQL SELECT c.cashier_code,SUBSTR(c.BOOKINGNO,0,2) PUBLISHER,c.rcptno,c.rcptdt RECEIPTDATE, ag.name, m.TYPE, c.rate_type RATETYPE, m.cat_name CATEGORY,SUM(a.amount) AMOUNT,c.paymode PAYMODE, c.check_no

FROM MAIN_CATEGORY m, classified_booking c, agency_Detail_confirm a, AD_AGMAST ag

WHERE c.bookingno=a.bookingno AND c.rcptno=a.rcptno AND c.rcptdt=a.rcptdt AND SUBSTR(c.BOOKINGNO,0,2)='DB'

AND c.rcptdt BETWEEN '20-Feb-2013' AND '31-Mar-2013' AND c.citycode='JAIP' AND m.category_id=c.category_id AND c.agcode=ag.agcode

GROUP BY m.TYPE,m.cat_name,c.category_id,c.rate_type,c.paymode,SUBSTR(c.BOOKINGNO,0,2),c.rcptdt,ag.name,c.rcptno,c.check_no,c.cashier_code

ORDER BY c.rcptdt,SUBSTR(c.BOOKINGNO,0,2),c.rate_type,m.TYPE,c.paymode,m.cat_name;

call StoreAndDrop 'D:\Qlikview\LIVEQVD\CLASSIFIED\', 'CLSCURRENT_MONTH_DB1';

Now my question is there we have given statically date in between "'20-Feb-2013' AND '31-Mar-2013" .I want it be done automatically like i should not give dates. I need data for every month and dates should be changed for every month.

I am using version 8.2

Can you please help me on this

Regards,

RBK

[kaushiknsolanki]

Hi,

     Best option is to remove that condition from the Sql Query, and include the same field in select.

     And in QlikView create a Month, Year and date out of it and use in front end as a listbox(Filter).

Regards,

Kaushik Solanki

[bharatkishore]

Thanks for the reply...

Can you please say to me more clearly...

[jagannalla]

Hi,

Please find this code

LET vLastMonth = Date(AddMonths(Today(),-1),'DD-MMM-YYYY');

LET vThisMonth =Date(MonthEnd(Today()),'DD-MMM-YYYY');

CLSCURRENT_MONTH_DB1:

LOAD *;

SQL SELECT c.cashier_code,

SUBSTR(c.BOOKINGNO,0,2) PUBLISHER,

c.rcptno,

c.rcptdt RECEIPTDATE,

ag.name, m.TYPE, c.rate_type RATETYPE, m.cat_name CATEGORY,SUM(a.amount) AMOUNT,c.paymode PAYMODE, c.check_no

FROM MAIN_CATEGORY m, classified_booking c, agency_Detail_confirm a, AD_AGMAST ag

WHERE c.bookingno=a.bookingno AND c.rcptno=a.rcptno AND c.rcptdt=a.rcptdt AND SUBSTR(c.BOOKINGNO,0,2)='DB'

AND c.rcptdt BETWEEN '$(vLastMonth)' AND '$(vThisMonth)' AND c.citycode='JAIP' AND m.category_id=c.category_id AND c.agcode=ag.agcode

GROUP BY m.TYPE,m.cat_name,c.category_id,c.rate_type,c.paymode,SUBSTR(c.BOOKINGNO,0,2),c.rcptdt,ag.name,c.rcptno,c.check_no,c.cashier_code

ORDER BY c.rcptdt,SUBSTR(c.BOOKINGNO,0,2),c.rate_type,m.TYPE,c.paymode,m.cat_name;

Hope it helps you

Cheers!!

Jagan