Set Analysis for Duplicates

[Mitch_Data]

Hi guys,

I have made a set analysis that is working absolutely fine without any filters selected, see below:

So with this set analysis I am counting duplicate values for 'Kenteken'.

Count({< Kenteken = {'=Count(Kenteken) >1'}>} Kenteken)

However, I have also made a set analysis averaging the value of group Deb Nr (Per group of clients). See set analysis below.

Avg({$<[Verkoop Resultaat] -={'X'},
Intercompany -={'intercompany'},
Kenteken={'=Count(Kenteken) =1'}>}
([Verkoop Resultaat] + [Tot Hsb] + [Verzekering en Schade] + [Tot ROB]+ [Tot VV] + [Tot MFAK] + [Tot Comm Afslag] + [Tot Contr Aanp Beeind]))

What happens, when I select Deb Nr as filter to check the group value the duplicate values change to 0, because some 'Kenteken' belong to different Deb Nr, however it should still be counted as a duplicate because it is.

Could you provide an idea how the duplicate measure can be altered to see the duplicate value regardless of any filter, so that it always shows 1 even when I select any filter?

Basically it looks like this when I have no filter or filter selected on 'Kenteken'

Now when I select Deb Nr as filter it looks like this.

See red marked. It now says 0 for duplicate, as opposed to the 1 earlier. That is because this 'Kenteken' doesn't occur twice for this Deb Nr, but it is a duplicate. How can I show a 1 now?

[anthonyj · Accepted solution]

Hi @Mitch_Data ,

If it's working before you start adding filters you can prevent the interaction with filters by adding {1} to the set analysis. In your example you have GT-153-V with Deb Nr 42001 and 4202071. When you choose 4202071 the count(Kenteken) will now only return one instead of 2. You can prevent this interaction so the inner {"=count(Kenteken)>1"} will ignore all selections.

Try adding like this:

Count({< Kenteken = {'=Count( {1} Kenteken) >1'}>} Kenteken)

I hope this helps

Regards

Anthony

[developer01]

use distinct under count exression

[Mitch_Data]

Thanks anthonyj, 

It worked perfectly.

Now I have another question you might be able to answer. You see I want to calculate the average per "Debnr".

I have the set analysis below

Avg(TOTAL{1}{<[Verkoop Resultaat] -={'X'},
Intercompany -={'intercompany'},
Duplicate ={'1'}>}
([Verkoop Resultaat] + [Tot Hsb] + [Verzekering en Schade] + [Tot ROB]+ [Tot VV] + [Tot MFAK] + [Tot Comm Afslag] + [Tot Contr Aanp Beeind]))

This works to show the total average of all "Debnr" while excluding some fields. It looks like this in the table:

As you may see it puts the same average for the whole table, and when I select Debnr as a filter it changes to a different value, it then takes the average of that Debnr. 

This is how it looks for total (the same value for the whole table , which changes when a debnr filter is selected)

My question is: Do you know how to show the average of per Debnr regardless of filter so that it basically puts the average of the filtered value in the table when no filter is selected. So lets say I take 4202071 as an example again it looks like this: - How can I change the unfiltered table to show that value below for 4202071 (and thus a different number for every Debnr) Any recommendations, many thanks!

[anthonyj]

Hi @Mitch_Data ,

The problem is in the use of the TOTAL function. This puts the average across all the values in the set even though you would have another dimension in your table. It basically causes the calculation to ignore the dimension in the table which is a very useful feature. In your case however you need it to ignore the Kenteken and aggregate over Debnr so the syntax looks like this: TOTAL <Debnr>.

Avg(TOTAL <Debnr> {1<[Verkoop Resultaat] -={'X'},
Intercompany -={'intercompany'},
Duplicate ={'1'}>}
([Verkoop Resultaat] + [Tot Hsb] + [Verzekering en Schade] + [Tot ROB]+ [Tot VV] + [Tot MFAK] + [Tot Comm Afslag] + [Tot Contr Aanp Beeind]))Avg(TOTAL{1}{<[Verkoop Resultaat] -={'X'},
Intercompany -={'intercompany'},
Duplicate ={'1'}>}
([Verkoop Resultaat] + [Tot Hsb] + [Verzekering en Schade] + [Tot ROB]+ [Tot VV] + [Tot MFAK] + [Tot Comm Afslag] + [Tot Contr Aanp Beeind]))

I also shifted the {1} into the set analysis. This will prevent any interaction with any dimension in the report.

Experiment with this in your table. If you have Kenteken and Debnr in your table as dimensions the value will duplicate for each Kenteken row because the TOTAL is calculating at the Debnr level.

Regards

Anthony