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: 
wim_rijken
Partner - Contributor II
Partner - Contributor II

Set Analysis: add and exclude values on same field

Hello,

I want to create a list of customer which have revenue in a certain period, but at the same time have no revenue in another period.

The periods are variable via 2 slider-objects.

I have very simple sample data:

fact:

LOAD * INLINE [

    PostingDate, CustomerNo, Revenue

    01/01/2015, KL1, 1000

    01/01/2016, KL1, 1500

    01/04/2015, KL2, 800

    01/11/2015, KL2, 500

    01/03/2015, KL2, 300

];

I have created 2 sliderobjects with min and max value:

Slider object 1 fills 2 variables: vMinDateValueRevenue / vMaxDateValueRevenue

Slider object 2 fills 2 variables: vMinDateValueNoRevenue / vMaxDateValueNoRevenue

I have created a straight table with dimension "Customer" and expression "Revenue".

Now I want to show as result:

Sum of Revenue within the selected period between vMinDateValueRevenue and vMaxDateValueRevenue, but only if there is NO revenue in the period between vMinDateValueNoRevenue en vMaxDateValueNoRevenue.

I assume this can be done via Set Analysis, but I cant find the correct syntax.

Can anybody assist?

1 Solution

Accepted Solutions
swuehl
MVP
MVP

Maybe something like

=Sum({<

PostingDate = {">=$(=Date(vMinDateValueRevenue))<=$(=Date(vMaxDateValueRevenue))"},

CustomerNo = {"=Sum({<Posting Date = {">=$(=Date(vMinDateValueNoRevenue))<=$(=Date(vMaxDateValueNoRevenue))"}>}Revenue)=0"}

>} Revenue)

Double check that the date variables are correctly expanded in the set modifier:

Dates in Set Analysis

Same could maybe also be achieved using p() and e() function:

=Sum({<

CustomerNo =

p({<PostingDate = {">=$(=Date(vMinDateValueRevenue))<=$(=Date(vMaxDateValueRevenue))"} >} )

*

e({<Posting Date = {">=$(=Date(vMinDateValueNoRevenue))<=$(=Date(vMaxDateValueNoRevenue))"} >} )

>} Revenue)

View solution in original post

4 Replies
sunny_talwar

For your sample what would be good values for setting the variables and the expected output?

swuehl
MVP
MVP

Maybe something like

=Sum({<

PostingDate = {">=$(=Date(vMinDateValueRevenue))<=$(=Date(vMaxDateValueRevenue))"},

CustomerNo = {"=Sum({<Posting Date = {">=$(=Date(vMinDateValueNoRevenue))<=$(=Date(vMaxDateValueNoRevenue))"}>}Revenue)=0"}

>} Revenue)

Double check that the date variables are correctly expanded in the set modifier:

Dates in Set Analysis

Same could maybe also be achieved using p() and e() function:

=Sum({<

CustomerNo =

p({<PostingDate = {">=$(=Date(vMinDateValueRevenue))<=$(=Date(vMaxDateValueRevenue))"} >} )

*

e({<Posting Date = {">=$(=Date(vMinDateValueNoRevenue))<=$(=Date(vMaxDateValueNoRevenue))"} >} )

>} Revenue)

wim_rijken
Partner - Contributor II
Partner - Contributor II
Author

In the first solution, there seems to be a problem (the last part of the syntax of "CustomerNo" is underlined in red

CustomerNo = {"=Sum({<Posting Date = {">=$(=Date(vMinDateValueNoRevenue))<=$(=Date(vMaxDateValueNoRevenue))"}>}Revenue)=0"}

the second solution seems to be working perfectly.

Thanks!!

swuehl
MVP
MVP

Ah, I think that's because nested advanced searches (advanced search enclosed  in double quotes embedded into advanced search enclosed in double quotes). You need to replace one pair with single quotes:

CustomerNo = {"=Sum({<Posting Date = {'>=$(=Date(vMinDateValueNoRevenue))<=$(=Date(vMaxDateValueNoRevenue))'}>} Revenue)=0"}

After I posted my last answer I reviewed and then assumed that the first one should be logically correct, while the second one would filter the correct CustomerNo, but should not restrict the Revenue to the selected date range.

I think this should be correct for this requirement:

=Sum(

{<

PostingDate = {">=$(=Date(vMinDateValueRevenue))<=$(=Date(vMaxDateValueRevenue))"},

CustomerNo =

          e({<Posting Date = {">=$(=Date(vMinDateValueNoRevenue))<=$(=Date(vMaxDateValueNoRevenue))"}>})

>} Revenue)