Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

wim_rijken
New Contributor

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
MVP
MVP

Re: Set Analysis: add and exclude values on same field

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)

4 Replies

Re: Set Analysis: add and exclude values on same field

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

MVP
MVP

Re: Set Analysis: add and exclude values on same field

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
New Contributor

Re: Set Analysis: add and exclude values on same field

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!!

MVP
MVP

Re: Set Analysis: add and exclude values on same field

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)


Community Browser