topic Re: Excluding date range with in date range in QlikView

Excluding date range with in date range

nareshthavidishetty — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

Currently we were capturing the data from April 1st to till date.We have hard coded the start date means April 1st as an variable as

VstartDate = 11060401 and Vcurrentdate as 11060609 then we use these variables in the logic to get the data..

Now we got an change request to exculde the date range like they want to exculde the data in between 26th april to 30th April..

AS: Sum(sales) from 11060401 to 11060609 (Excuding sum(sales) from 11060426 to 11060430).

date field : Sales_Date

Thanks...

Re: Excluding date range with in date range

sunny_talwar — Thu, 09 Jun 2016 18:35:35 GMT

May be like this:

Sum({<VstartDate = {'>=11060401<=11060426', '>=11060609<=11060430'}>}sales)

Sum({<VstartDate = {'>=11060401<=11060426', '>=11060430<=11060609'}>}sales)

Re: Excluding date range with in date range

sunny_talwar — Thu, 09 Jun 2016 18:37:46 GMT

or this:

Sum({<VstartDate = {'>=11060401<=11060609'}-{'>=11060426<=11060430'}>}sales)

Re: Excluding date range with in date range

nareshthavidishetty — Fri, 10 Jun 2016 05:54:21 GMT

Hi,

We have filter the data at script level...

below is the script..

LOAD *;

SELECT A.A7AACD ,B.BBR2TT as BBR2TT_MTD_Mule , COUNT(DISTINCT B.BBBANB) as count_based_on_mule_status_MTD

FROM DSA7CPP A , INBBCPP

B WHERE A.A7BANB = B.BBBANB AND A.A7AIDT >= $(11060401) AND A.A7AIDT <= $(vyesterday)

GROUP BY A.A7AACD,B.BBR2TT ;

Where vYesterday = today()-1

So in between from 11060401 to vyesterday ineed to exculde the data from 11060426 to 11060430.

Thanks..

Re: Excluding date range with in date range

nareshthavidishetty — Fri, 10 Jun 2016 08:56:38 GMT

Hi,

Any suggestions...

Re: Excluding date range with in date range

sunny_talwar — Fri, 10 Jun 2016 09:53:24 GMT

May be like this:

LOAD *;

SELECT A.A7AACD ,B.BBR2TT as BBR2TT_MTD_Mule , COUNT(DISTINCT B.BBBANB) as count_based_on_mule_status_MTD

FROM DSA7CPP A , INBBCPP

B WHERE A.A7BANB = B.BBBANB AND ((A.A7AIDT >= 11060401 AND A.A7AIDT <= 11060426) OR (A.A7AIDT >= 11060430 AND A.A7AIDT<= $(vyesterday)))

GROUP BY A.A7AACD,B.BBR2TT ;

Re: Excluding date range with in date range

nareshthavidishetty — Fri, 10 Jun 2016 10:09:49 GMT

Hi,

I got the below error after the script run..

Thanks..

Re: Excluding date range with in date range

sunny_talwar — Fri, 10 Jun 2016 10:12:05 GMT

Seems like an issue with your variable here. Was it working before we added the new code (the variable I mean)?

Re: Excluding date range with in date range

nareshthavidishetty — Fri, 10 Jun 2016 10:20:21 GMT

Thanks..

Issue with the variable.

Issue got resolved thanks..