Question

Alternative of group by in Pigment

  • 12 March 2024
  • 1 reply
  • 65 views

Hello Pigment!

I am having an issue with a formula.

I have a dimension called 'MRR-D Invoice '. I want to create the column “latest_suscription_duration”  which takes the same value as“subscription_duration” according to the max end date, and that, for each customer. 

This is similar to a group by function in sql. Anyone knows how to do that ?   

I tried this but it gave me weird results because I didn’t group by customer reference :

'MRR-D Invoice .'Subscription_duration'[BY max 'MRR-D Invoice '.'End_date' ] 

PS : End_date is a dimension, Subscription_duration is an integer

Customer_reference End_date  Subscription_duration  Latest _subsciption_duration
Customer A 24-Jan 3 3
Customer A 21-Jan 12 3
Customer A 23-Jan 4 3
Customer B 24-Feb 12 12
Customer B 23-Feb 12 12

1 reply

Userlevel 6
Badge +12

Hi Ouma,

 

I’ve tried to make a quick repro of your model.

The easiest way I’ve found was to add new properties in my Customer dimension, since you want to know the max end date per customer and the lastest duration attached per customer.

Then, if needed it could be applied it on all lines of the MRR-D Invoice dimension.

 

 

First, here is my source data : MRR-D Invoice dimension

In green I highlighted the values we want to return per customer on the Latest Subscription duration column.

I made a few other examples for when a customer has only one subscription, or two on the same day.

 

Then I opened my Customer dimension and created two properties:

 

Last subcription end date

formula is

'MRR-D Invoice'.End_date [by max: 'MRR-D Invoice'.Customer_reference]

I looking for the maximum value of end dates per Customer

 

and

Latest subcription duration

if(('MRR-D Invoice'.End_date = Customer.'last subscription end date'), 'MRR-D Invoice'.Subscription_duration)[by lastnonblank: 'MRR-D Invoice'.Customer_reference]

I compare the end date in the MRR-D invoice dimension with the max end date from my Customer Dimension, if it matches, I return the duration per customer with a lastnonblank sort, meaning, if I have two results (like the Black customer here in my example), I will return the last result, but I can change to firstnonblank if needed.

 

Now that I have my values (Last date + Last duration) per Customer, I can return them on my MRR-D Invoice dimension if needed.

 

Latest_Subcription_duration

'MRR-D Invoice'.Customer_reference.'Latest_subscription duration'

I am doing a chaining to return the property Latest subscription duration from the Customer dimension using the Customer property of the MRR-D Invoice dimension.

 

Hope this helps.

 

Best,

 

Reply