topic Re: DATE not getting incremented in QlikView

DATE not getting incremented

master_student — Fri, 21 Oct 2016 09:15:00 GMT

I would like to get the result my whole query by date of extract, the result would be like this :

and so on for extract date between vDWDate and vToday

Here's my script :

let vDWDate = makedate(2016,9,26);

let vToday = makedate(2016,10,2);

for iDaysBack = 0 to (vToday - vDWDate)

let vNextDay = Date(vToday - iDaysBack, 'DD/MM/YYYY');

test:

load *

;

select

inv.billing_period,

case when nb_contract_actif > 0 THEn 'a' else 's' end as status

,ct.customer_type_name ,

coalesce(ca.advisor,'Inconnu') as advisor,

segments.segment_name,

sum( case when g.cnt >0 then total_invoice_amount/g.cnt else total_invoice_amount end )

as CA,

to_date('$(vDWDate)','DD/MM/YYYY')as datee

from

......

where to_date('$(vDWDate)' ,'DD/MM/YYYY') between cas.valid_from and cas.valid_to

.....

group by 1 ,2 ,3,4 ,5, datee;

The result is getting incremented as you can see bellow : but the date field is 26/09/2016 (datee) is not getting incremented. it displays 26/06/2016 the minimum date

do you have any idea please??

Re: DATE not getting incremented

sunny_talwar — Fri, 21 Oct 2016 10:38:23 GMT

I think you needed to use vNextDay instead of vDWDate in your Select statement because that is the variable which is changing and not vDWDate

LET vNextDay = Date(vToday - iDaysBack, 'DD/MM/YYYY');

test:

load *

;

select

inv.billing_period,

case when nb_contract_actif > 0 THEn 'a' else 's' end as status

,ct.customer_type_name ,

coalesce(ca.advisor,'Inconnu') as advisor,

segments.segment_name,

sum( case when g.cnt >0 then total_invoice_amount/g.cnt else total_invoice_amount end )

as CA,

to_date('$(vNextDay)','DD/MM/YYYY')as datee

from

......

where to_date('$(vNextDay)' ,'DD/MM/YYYY') between cas.valid_from and cas.valid_to

.....

group by 1 ,2 ,3,4 ,5, datee;

Re: DATE not getting incremented

master_student — Fri, 21 Oct 2016 10:59:13 GMT

Thanks Sunny. You are right , I have one pb is that the date field is not well sorted on the graph:

Re: DATE not getting incremented

sunny_talwar — Fri, 21 Oct 2016 11:01:17 GMT

Why don't you sort on this?

Re: DATE not getting incremented

master_student — Fri, 21 Oct 2016 12:03:13 GMT

Yes. It works. Thanks Sunny.