Announcements
The way to achieve your own success is the willingness to help somebody else. Go for it!
cancel
Showing results for
Did you mean:
Creator

## Set Analysis between a range of dates

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?

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

CAFC

1 Solution

Accepted Solutions
Partner - Master

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)

30 Replies

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)

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Contributor III

Hi Cedric

Could you please share a dummy data set.

Thanks

Ankur

Creator
Author

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?

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

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Creator
Author

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

thanks

Creator
Author

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

thanks

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"

Are you expecting 244??

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Creator
Author

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

thank you

Community Browser