Distinct counting flags using Set Analysis

[#1]

Hello,

I want to develop an application to show how many customers (identified by a reference number) buying from a certain distributor are also buying from another one. For this a distributor/distributor-table is to be built.

The data model is based on a SALES table from which the SALESFLAGS table is derived to flag each SALES entry if the reference bought from a distributor this certain month. In each column of the application table Set Analysis and Count Distinct is used to count the flags (yet each reference is to be counted only once!).

In the original application I associated both tables with a concatenated field of YEAR&REFERENCE which worked for a year. By accident I missed the month so now the association is made by YEAR&MONTH&'_'&REFERENCE and this way it works well for a month - yet it shows false values when all the months of the same year are selected.

I tried to break down the application to a simpler example in the attachment: there are three references (1,2,3) buying from three distributors (A,B,C). With no selections over the months 1 and 2 are buying everywhere and 3 only bought from C once. According to the desired logic there should be a 2 in all columns and rows except for C/C with a value of 3. Sadly this is not the case as C/A, C/B and so on are not.

If the association between the two tables is made via YEAR&REFERENCE (see the comment in the script) it suddenly works for the year 2015 - but by selecting a month it doesn't work anymore either (e.g. select January: only reference 1 is buying from A and B, so the A-row should have the values 1,1,0 but instead 1,1,1 is shown).

Up to now I may only choose between correct values for a year (associating both tables via YEAR&REFERENCE) or a month (connecting them via YEAR&MONTH&'_'&REFERENCE) - is there a way to make both views work with one data model and one application table?

Thanks in advance for any advice

[#2 · Accepted solution]

OK this has been a tough one but I seem to have a solution.

Firstly I had to use data islands so no connection is made by Qlikview at all. This still means cloning (load resident) the year, month and reference into a flag table but also renaming them like flYEAR.

Everything else is made in the diagram itself by using Aggr(), Set Analysis and intersections of them- see the attachment. In fact I defined every cell on its own by using IFs but that works for now.

In the business application I had to set some more filters which also had to be cloned (load resident) into the data island and selected by Set Analysis in the aggr (not tested for syntax):

   count({<flYEARMONTH=p(YEARMONTH),flDISTRIBUTOR_COUNTRY=p(DISTRIBUTOR_COUNTRY),flREFERENCE={"=min(aggr(max( {<flYEARMONTH=p(YEARMONTH),flDISTRIBUTOR_COUNTRY=p(DISTRIBUTOR_COUNTRY)>} isA),flREFERENCE))>0"}>} distinct flREFERENCE)

It looks awfully complicated but as long as it's working and I don't have to maintain it ...