topic Re: Set Analysis between a range of dates in QlikView

Set Analysis between a range of dates

cafcptg2011 — Wed, 02 Aug 2017 11:03:45 GMT

Hi,

I need to make an expression that returns the costumers with sales betweeen 2 dates and without sales between 2 other dates...

my current expression is:

=sum({< dat = {">=$(=date('01-01-2016','DD-MM-YYYY')) <=$(=date('31-08-2016','DD-MM-YYYY')) "} , dat -= {">=$(=date('01-09-2016','DD-MM-YYYY')) <=$(=date('31-12-2016','DD-MM-YYYY')) "} >} Sales)

but this expression doesn't work...

I need sales between 01-01-2016 and 31-08-2016 and with sales = 0 between 1-09-2016 and 31-12-2016..

and if want to know sales same period (01-01-2016 and 31-09-2016) but great then 1000 for instance... how can I accomplish that?

could some please help me?

Thanks in advance,

edit: the result that I want is here in this picture:

CAFC

Re: Set Analysis between a range of dates

Anil_Babu_Samineni — Wed, 02 Aug 2017 11:10:05 GMT

Does you mean this? I assume your date field has same format like "DD-MM-YYYY"

=sum({< dat = {">=$(=date('01-01-2016','DD-MM-YYYY')) <=$(=date('31-09-2016','DD-MM-YYYY')) "}, Instance = {'>1000'} >} Sales)

Re: Set Analysis between a range of dates

ankur_abhishek — Wed, 02 Aug 2017 11:11:11 GMT

Hi Cedric

Could you please share a dummy data set.

Thanks

Ankur

Re: Set Analysis between a range of dates

cafcptg2011 — Wed, 02 Aug 2017 11:15:04 GMT

no, is not want I mean... sorry..

I need the get costurms with sales between 01-01-2016 and 31-08-2016 and with sales = 0 between 1-09-2016 and 31-12-2016..

do you understand??

and then I ask by the way how could I accomplist to get Sales in this range 01-01-2016 and 31-08-2016 but with Sales> 1000... right?

Re: Set Analysis between a range of dates

Anil_Babu_Samineni — Wed, 02 Aug 2017 11:22:03 GMT

Will you able to provide sample data set to test? Because, your explanation hearing pretty easy but not sure how i can offer you with out seeing any data

Re: Set Analysis between a range of dates

cafcptg2011 — Wed, 02 Aug 2017 11:24:36 GMT

ok. I have edit my post and attached an exemple of my data..

thanks

Re: Set Analysis between a range of dates

cafcptg2011 — Wed, 02 Aug 2017 11:24:48 GMT

ok. I have edit my post and attached an exemple of my data..

thanks

Re: Set Analysis between a range of dates

Kushal_Chawda — Wed, 02 Aug 2017 11:26:41 GMT

try this

=sum({< dat = {">=01-01-2016 <= 31-08-2016 "} , dat -= {">=01-09-2016<=31-12-2016"} >} Sales)

make sure that your dat format is "DD-MM-YYYY"

Re: Set Analysis between a range of dates

Anil_Babu_Samineni — Wed, 02 Aug 2017 11:29:11 GMT

Are you expecting 244??

Re: Set Analysis between a range of dates

cafcptg2011 — Wed, 02 Aug 2017 11:32:00 GMT

your expression is the same of the mine!! and it doesn't work..

thank you

Re: Set Analysis between a range of dates

Anil_Babu_Samineni — Wed, 02 Aug 2017 11:36:02 GMT

One last question from end, Why are you not showing costumer of 1 and 3 even those dates are with in the date ranges only. Just for confirmation

Re: Set Analysis between a range of dates

Kushal_Chawda — Wed, 02 Aug 2017 12:07:43 GMT

what is the format of dat field?

Re: Set Analysis between a range of dates

cafcptg2011 — Wed, 02 Aug 2017 12:54:18 GMT

I'm expecting 182 and not 244...

why?

because the costumer 1 and 3 has sales one the between 1-09-2016 and 31-12-2016. I need only the costumer who has only sales in period of 1-1-2016 to 31-08-2016 and not between 1-9-2016 to 31-12-2016.

Re: Set Analysis between a range of dates

Kushal_Chawda — Wed, 02 Aug 2017 13:03:08 GMT

this expression is working for me.. please try in your application

=sum({<dat={">=01-01-2016<=31-08-2016"},dat-={">=01-09-2016<=31-12-2016"}>}sales)

Re: Set Analysis between a range of dates

Kushal_Chawda — Wed, 02 Aug 2017 13:04:49 GMT

Re: Set Analysis between a range of dates

cafcptg2011 — Wed, 02 Aug 2017 13:30:57 GMT

I'm expecting 182 and not 244... sorry

thank you

Re: Set Analysis between a range of dates

Kushal_Chawda — Wed, 02 Aug 2017 13:33:11 GMT

In your attached application, there is only one customer who with Date 1-9-2016, so out of total 271, 244 is should be the total

Re: Set Analysis between a range of dates

cafcptg2011 — Wed, 02 Aug 2017 13:35:27 GMT

no.. the costumer 1 and 3 have sales after 1-9-2016...

Re: Set Analysis between a range of dates

Kushal_Chawda — Wed, 02 Aug 2017 13:39:25 GMT

Yes so, two customer, having 12+15=27, so total 271-27=244.. is it not correct?

Re: Set Analysis between a range of dates

Kushal_Chawda — Wed, 02 Aug 2017 13:40:42 GMT

Why you are not trying this expression in your actual application and see the results?