Skip to main content
Question

Counting active customers between a Start date and End date

  • December 24, 2020
  • 2 replies
  • 34 views

Hello, I'm a new FME user and normaly only work with Tableau. I'm used to create calculations inside Tableau but now I want to do this with FME so I can fill our new datawarehouse.

 

I've got a dataset with a start date (dd_begin) and an end date (dd_eind). I need to count distinct the customer id's per Month. In Tableau I've created 12 calculations per year. For example, if I want to count the number of unique customers in January 2020 I need below Tableau calculation:

 

COUNTD(

if [DD_BEGIN] < date(#01-02-2020#) and ([DD_EIND] >= date(#01-01-2020#) or ISNULL([DD_EIND])) then [CLIENTNR] end)

 

for February 2020 the calculation would be:

 

COUNTD(

if [DD_BEGIN] < date(#01-03-2020#) and ([DD_EIND] >= date(#01-02-2020#) or ISNULL([DD_EIND])) then [CLIENTNR] end)

 

And so on and after this I use those 12 calculated fields to create a bar chart for the entire year so every month is visible inside 1 visual in Tableau.

 

How can I do the same calculations in FME or is there an easier way to get the same data result?

 

Kind regards,

Richard

2 replies

  • December 29, 2020

Good question,

Assuming your table has individual dates per row, you can use the StatisticCalculator transformer to group the two attributes together to give you your SUM of customers.

Hope this helped!​


Forum|alt.badge.img+2
  • December 29, 2020

@sanchezenschede​  Welcome to the FME community. If you're comfortable with SQL, you can also try the InlineQuerier, or if the data is already in a source database SQLExecutor


Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings