Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

RFM Model - How to create a Cluster Record to visualize evolutiong throught time?

Hello Everyone!! I created some sort of a Cluster usind the RFM Model (Recency, Frequency, Monetary) directly in a calculed collumn, so with that I can have the name of the cluster for each one of m...
General Comment
Need Help
Tips and Tricks
Anonymous's avatar
Anonymous
Not applicable
5 years ago

Yes MFelix 

every refresh overlays the data =(

MFelix's avatar
MFelix
Icon for Super User rankSuper User
5 years ago

Hi Anonymous ,

 

Do you want to calculate this based on the update date of the information or on the order date?

 

I have prepared a setup based on a date dimension table and the difference to the selected value of the orders. 

 

So basically just replace your TODAY() from your days without orders to a MAXIMUM date, and all the formulas are with measures.

 

I will send the explanation and file between today and tomorrow.

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    MFelix 

    I want to calculate based on the update date, for example: How many of each clusters I had on December 1st, how many on December 2nd, throught every day. Also would be nice to see how many of each clusters through the months:

    How many cluster when I was in 31 October, 30 November, and so on...

    Did you get it?

    Thank you very much!!

    • MFelix's avatar
      MFelix
      Icon for Super User rankSuper User
      5 years ago

      Hi Anonymous ,

       

      You don't have an updated date on your file. So you are not saving daily information, you refer that you overwritte the data so without that date this is not possible to do.

       

      Regarding month by month I have everythi  prepared.

      • MFelix's avatar
        MFelix
        Icon for Super User rankSuper User
        5 years ago

        Hi Anonymous ,

         

        I created the following measures:

         

        Last Order Date = MAX(Orders[created_at])

Days Without Order Number = DATEDIFF([Last Order Date];MAX(DimDate[Date]);DAY)

Recency Note value = 
SWITCH (
    TRUE ();
    [Days Without Order Number] < 7
        && [Last Order Date] <> BLANK (); 5;
    [Days Without Order Number] >= 7
        && [Days Without Order Number] < 14; 4;
    [Days Without Order Number] >= 14
        && [Days Without Order Number] < 30; 3;
    [Days Without Order Number] >= 30
        && [Days Without Order Number] < 60; 2;
    [Days Without Order Number] >= 60
        && [Days Without Order Number] < 90; 1;
    0
)

         

        Also created a dimension table with dates and another one with the clusters:

        IDCluster

        5 Champions
        4 Loyal
        3 Potencial Clientes
        2 Need Attention
        1 Almost Lost
        0 Lost

         

         

        Now add the following measure:

        Cluster_Count =
VAR temptable =
    SUMMARIZE ( Clients; Clients[id]; "Recency"; [Recency Note value] )
RETURN
    IF (
        MAX ( DimDate[Date] ) <= MIN ( Orders[created_at] );
        BLANK ();
        CALCULATE (
            COUNTROWS ( FILTER ( temptable; [Recency] IN VALUES ( 'Cluster'[ID] ) ) )
        )
    )

         

        See result below:

         

         

        The two slicers on the bottom are for the order dates and for the date dimension  table that will allow to have filters. these two slicers are sincronized with each other.

         

        Check file attach, and if you need any further assistance please tell me.