Set Analysis between a range of dates

[cafcptg2011]

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

[cafcptg2011]

if one costumer has sales on period from 01-09-2016 to 31-12-2016 the all sales from that costumer are excluded...

so for that reason you can't sum sales from the costumer 1 and 3.. so the result is only 182 (which is the sales from the costumer 2 and 4)

[stigchel · Accepted solution]

To exclude the customers with sales for those dates:

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

[Kushal_Chawda]

Now I got it, then use below

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

[stigchel]

[Kushal_Chawda]

stigchel‌ you are faster

[cafcptg2011]

Hi, your answer is correct, for the first part of my post...

But my second question was if I only want sum of sales from 1-1-2016 to 31-08-2018 above X , X could be 1000 or other number... I can accomplish that? and is not putting sales={">3000"} in your expression that will work...because I have already try it...

[stigchel]

Do you want to include or exclude them? The E() stands for Exclude, P() for possible

Include customers with sales above 1000

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

[cafcptg2011]

in your expression it is enough to make if (expression > 1000, expression)... that's what i want...

thank very much for your help...

Regards.

[cafcptg2011]

just a little thing.... when I clik over one costumer in my pivot table... it doesn't filter the costumer... it shows all costumers....

i think it was 1 in you expression... but is not...

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

do you know why is happeing that??

thanks..

[stigchel]

If you want to also filter for these 'costumers'

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