Recalculate the transformation of the dataflow based on the specific time range selected in the card

RobertoBis
RobertoBis Member
in Magic ETL

Hi everyone!

 

I am trying to create a card to show the number of active customers. Active customers are those whose the sum of revenue and the sum of quantity are more than 0.

Practically, it means to exclude those customers whose the sum of revenue and the sum of quantity are less than or equal than zero.

 

The reason behind that logic is that a customer could make an order and then return it some days later. Since in the data Source both those transactions are recorded, there will be a date in which the revenue and the quantity are positive, and a later date in which the same amount of revenue and quantity will be negative, leading to 0 the sum of those two metrics, for that specific customer.

 

At this purpose, I'm using a dataflow; I've created a list of those customers whose respect the above conditions in all their lifetime. Unfortunately, this approach does not fully answer to the request, because we would want to exclude the inactive customers within the selected period in the card.

 

Let me make an example: if a customer orders a product on the 28th of September and returns it on the 3rd of October, selecting as a date range in the card the only month of September, this customer should be considered active (because he has not done the return yet).

For the same reason, if the users selects in the card September + October in the date range, the same customer should be considered inactive, because the sum of revenue and quantity would be 0.

 

In other words, we should find a way to recalculate the transformation of the dataflow based on the specific time range selected in the final card.

 

Do you have any suggestion to do this?

 

Many Thanks!
Roberto

Tagged:

Comments

  • Jarvis
    Jarvis Domo Employee

    Hi Roberto. 

     

    It sounds like you are almost there. My understanding is that you have built the flow already but I am not sure if you have built a card and tested the result. Can you confirm? If you have and you are having this issue then I would reach out directly to Support and ask for them to review this dataflow and offer their suggestions. Once they have their eyes on this the solution may come to light quickly.

     

    I hope this link helps you with the support portal;

    https://knowledge.domo.com/Welcome/Getting_Started/Understanding_the_Customer_Support_Portal

     

    Jarvis

  • RobertoBis
    RobertoBis Member

    Hi Jarvis, 

    thanks for your reply.


    I've created the card but it does not work as I expect. Since I've pre-filtered in the dataflow those customers whose the sum of revenue and quantity is > 0, I'm excluding them, independently from the time frame selected in the card.


    What I would want to have is to consider active/inactive a customer depending on the date range selected in the card. In my previous example, thus, If I select in the card the period Jan-Sep I would want to see that customer active; on the contrary, selecting in card the period Jan-Oct I would want to see that customer inactive.

     

    Thanks,
    Roberto

     

Categories