Set analysis difference

gp_oconnor — Wed, 25 Nov 2020 16:16:04 GMT

I have the following requirement for a measure:

Calculate the number of shoppers in a particular period who did not shop in the previous 12 months.
The current period is indicated by a date type of “TP” and the previous 12 months indicated by date types of “LP”,”LA”,”IX” and “TB”.

The set analysis expression in the sample app attached would appear to be structured correctly [using an exclusion operator (-)]. However, the result is not as expected.

What is confusing here is that the union operator + works. The sole difference between the union and the exclusion being the + changed to a (-).

If anyone could shed some light on this or point me in the right direction, that would be much appreciated.

From the research I've done and input from colleagues it would appear that the P() and E() functions aren't suitable as they don't take into account dimensionality in a chart.

Is it possible to create an expression using set analysis to provide the expected result without changing the data model?

Thanks

Re: Set analysis difference

gp_oconnor — Fri, 08 Jun 2018 10:45:14 GMT

Revisited this after close to 2 years. Found an alternative solution without changing the data model, using aggregation function. Expression below, amended app attached. It will probably have a high overhead for larger sets of data.

Sum(Aggr(if(Sum({<[PROM_DATE_TYPE]={'TP'},[LINK_LEVEL]={'50'},[COUNT_CASH_PCI]={'0'}>} [COUNT_PCI]) >

Sum({<[PROM_DATE_TYPE]={'LP','LA','IX','TB'},[LINK_LEVEL]={'50'},[COUNT_CASH_PCI]={'0'}>} [COUNT_PCI] ),1,0),

[PROM_ID],[COUNT_PCI]))

topic Set analysis difference in QlikView

Set analysis difference

Re: Set analysis difference