create a summary tabl

[KWHITEHURST]

Can anyone please assist, what i thought would be an easy task, has turned into a headache:

I have two tables which are linked by MainID

I am needing to summarise Policies (REFERENCENO) by PURCHASE MONTH and if their cover type is:

  Buildings Only
  Contents Only
  Joint (Buildings and Contents)
please see SS above of an example

I have tried creating set analysis formulas, Resident tables all with no success

any guidance would be greatly appreciated

many thanks

[KGalloway · Accepted solution]

What about this?

Each column in the summary table uses a slight variation of this formula:

buildings only =count(distinct if(wildmatch(aggr(concat(COVERTYPE), MAINID), '*buildings*') and not wildmatch(aggr(concat(COVERTYPE), MAINID), '*contents*') , MAINID))

contents only =count(distinct if(not wildmatch(aggr(concat(COVERTYPE), MAINID), '*buildings*') and wildmatch(aggr(concat(COVERTYPE), MAINID), '*contents*') , MAINID))

joint =count(distinct if(wildmatch(aggr(concat(COVERTYPE), MAINID), '*buildings*') and wildmatch(aggr(concat(COVERTYPE), MAINID), '*contents*') , MAINID))

The idea is that in each month, we check each MAINID's concatenated list of COVERTYPEs. If it contains Buildings and not Contents, then it was buildings only.

This does ignore every other COVERTYPE value.

Le8t me know if I can clarify anything.

[KWHITEHURST]

brilliant, it worked a treat, thank you very very much