Only count first occurence during a year in set analysis

[alliedarmour]

Hey,

I want to achieve the following in my straight table: Count records which appear in specific months during the year, but only count them if this is their first occurence during the year.

So for example: A customer visits a store in February, but paid a visit in January aswell. This customer should be counted for January (as it's the first visit of the year) but NOT for february.

Currently I'm achieving this by using the P() and E() functions, which works but is - in my opinion - a bit cumbersome with all those months you have to track during the whole year.

The example above looks like this with set analysis:

Count({$<CustID = P({1<Month= {"Jan.", "Feb."}, Year = {$(=Only(Year))} >}) *
E({1<Month = {"Jan."}, Year = {$(=Only(Year))}})>} DISTINCT CustID)

It's working and the values are correct - but I wonder if there is a more "elegant" solution to this. I tried with Min(Month), but the results didn't match.

Any ideas? Thanks for any input!

[BrunPierre]

Perhaps this.

Count({$<CustID = {"=Aggr(Min(Month), CustID, Year)"}>} DISTINCT CustID)

[alliedarmour]

Doesn't give me the same results, surprisingly. I tried something similar before.

[Or]

If this is always limited to a single year (which it appears to be, since you have a set in there for Only(Year)), why not just a regular count distinct?

If Month is a dimension, and you're looking for *new* customers for that month, probably the cleanest approach is to count distinct for the entire year up to and including that month, and subtract the Above() version of the same thing (or write it out the set for start of year to one month prior, which can be done with two aggregations or within the set itself using set subtraction - Set1(Start of total period to Endmonth of current date) - Set2(Start of total period to endmonth of AddMonths(Current date,-1))

[barnabyd · Accepted solution]

G'day @alliedarmour,

I like to get the processing done in the load script. Something like this:

MapFirstVisit:
mapping
load CustID & Year as [CustID-Year]
   , min( Month ) as [First Visit]
from Visit.qvd (qvd) group by CustID, Year;

Visit: 
load CustID, Year, Month 
   , if( Month = applymap( 'MapFirstVisit', CustID & Year ), 1, 0 ) as [First Visit]
from Visit.qvd (qvd);

Then the Set Analysis becomes very simple:

Count( {$<[First Visit] = {1}>} DISTINCT CustID )

The advantage is that this processing happens in the overnight load and not when the user selects a sheet or filter. It also makes it much easier to use in multiple visualisations. I hope this is useful.

Cheers, Barnaby.

P.S. I wasn't 100% sure but I understand that you want the first visit per Customer per Year.

[alliedarmour]

That's what I had before, which was even more cumbersome cause you have to do something like Set1(Jan, Feb, Mar,Apr,May,...) - Set2(Jan,Feb,Mar,Apr,...), so I replaced it with my P() and E()-Version.

The straight table always - per definition - shows the whole year, so the user doesn't select a month or specific period apart from a year, so I can't use the selection or a current date, unfortunately.

If you could provide a more "clean" code example I'd totally use it, but that's why I don't look for a How to do it (cause I know that) but rather for a Is there a more code-saving, elegant solution to do it 😅.

Thanks anyway for your input & time!

[alliedarmour]

Hey Barnaby,

yeah, you got me exactly right!

That's a quite elegant solution, will try it out tomorrow when I'm back at work, thanks!

Had also the idea to do it in background processing, but I didn't consider a MAPPING solution 🤔.