topic Re: Set Analysis: add and exclude values on same field in QlikView

Set Analysis: add and exclude values on same field

wim_rijken — Fri, 25 Mar 2016 14:40:55 GMT

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?

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

sunny_talwar — Fri, 25 Mar 2016 16:21:37 GMT

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

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

swuehl — Fri, 25 Mar 2016 17:20:18 GMT

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)

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

wim_rijken — Fri, 25 Mar 2016 19:33:43 GMT

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

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

swuehl — Sat, 26 Mar 2016 10:24:59 GMT

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)