Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
cafcptg2011
Creator
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?

could some please help me?

Thanks in advance,

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

Cap.PNG

CAFC

1 Solution

Accepted Solutions
stigchel
Partner - Master
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)

View solution in original post

30 Replies
Anil_Babu_Samineni

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)

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

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)
ankur_abhishek
Contributor III
Contributor III

Hi Cedric

Could you please share a dummy data set.

Thanks

Ankur

cafcptg2011
Creator
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?

Anil_Babu_Samineni

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

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

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)
cafcptg2011
Creator
Creator
Author

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

thanks

cafcptg2011
Creator
Creator
Author

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

thanks

Kushal_Chawda

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"

Anil_Babu_Samineni

Are you expecting 244??

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

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)
cafcptg2011
Creator
Creator
Author

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

thank you