Question

Keeping only the first non blank value of a metric

  • 6 December 2023
  • 6 replies
  • 290 views
G
Rank
Badge

Hello Pigment Community, 

I want to have a metric that has clients in lines and months in columns and that is filled with 1 only for the first month the client bought a thing. So in the end, on each line there should be only one 1 in the month of the first buy. 

For the moment I created a metric with clients in line and months in columns that is filled with 1 for all the months when the client bought a thing. My idea was to use this metric and the firstnonblank modifier but it does not work… 

Could you help me please ? 

6 replies

Benoit
Rank
Userlevel 6
Badge +12

Hi Grégoire,

 

I made a quick reproduction for you.

Here is a source list with Customers and Months where I highlithed the values I want to identify in my metric. I made a random order in months on purpose, and one customer with 2 transactions on the same month as well.

 

Here is my metric:

To identify the first month where a client bought a thing, I don’t use firstnonblank, instead to filter the non blank cells I use a condition if(isdefined() and to return a result only on the first month, I filter on the Month where the start date is the lowest using [by min: ]

Then I apply 1 on these cells, aggregating my results by Customers and Months.

Hence why on Customer Black, since they have 2 transactions on the same month, I have a “2”

 

Hope it helps you finding the correct formula for your case.

Let us know if you have further questions.

 

 

Benoit - Pigment wizard
G
Rank
Badge

Hi Benoit, thank you a lot for your answer I think it will help me a lot but I still haven’t managed to get what I want.

The thing is that I don’t have the same source list as you made in the reproduction. 
The source I have is a metric “Live Clients” that has clients and shops in rows and months in columns. It is filled with 1 if the client has bought something in this shop in the given month. 

To get closer to your source list I tried to create a second metric with the formula : “ IF(Live Clients = 1, Month)”. My idea is to have a metric with clients and shops in rows and months in column filled with the name of the month (instead of 1) if the client has bough something in this shop in the given month. 
But with this formula the whole metric is empty…

Would you have an idea to solve this ? 

Benoit
Rank
Userlevel 6
Badge +12

Hi again,

I added a Shop dimension to my list

I created a Live Clients metric which I hope relfects yours.

 

And now if I want to return the first Month by Customer and Shop, I use this formula:

 

So it’s structured by Customer and Shop, and the type = dimension : Month.

 

Am I getting closer to what you’re expecting maybe?

 

 

Benoit - Pigment wizard
G
Rank
Badge

Thanks a lot for your quick answer, you perfectly described my situation : live clients is exaclty the metric I have. 

I don’t know why but when writing the final metric Clients first month with the exact same formula as you it returns a completely blank metric without any month in it… 

I don’t think you can really do something about it it seems to be a bug but again thank you for your answer :) 

Benoit
Rank
Userlevel 6
Badge +12

Hi,

I believe your results are here but you’re not seeing them because your view is not pivoted at the most granular level.

You must have all your dimensions shown in Pivots to see the Months, this is because dimensions can’t be aggregated (Jan 23 + Feb 23 + blank = blank)

 

Can you try to play with the Pivot tab maybe?

Benoit - Pigment wizard
Benoit
Rank
Userlevel 6
Badge +12

Hi @Grégoire Leveillé ,

Do you confirm you’re able to see the results maybe?

 

I want to ensure you’re not facing a bug.

 

Many thanks,

Benoit - Pigment wizard

Reply


Ask your question here!

Terms & ConditionsCookie settings