Solved

Aggregating by sum for Months

  • 3 August 2022
  • 4 replies
  • 155 views
J
Rank
Userlevel 2
Badge +1
  • Jose Seasoned Pigmenteer
  • Seasoned Pigmenteer
  • 22 replies

Dears,

I would like to create a metric where I have the MRR BE, coming from the transaction list Requests MRR BE, summed by the QuickbooksId and by the Month, both of which are mapped as a dimension in my transaction list.

I have the following formula:

 

'Requests MRR BE'.'MRR BE'[BY:'Requests MRR BE'.QuickbooksId,'Requests MRR BE'.Month]

 

It works for QuickbooksId (ID number) , but it does not sum by Month. It ends up giving me the total per QuickbooksId (ID number) for every month of every year instead of allocating the sum of the ID per month of the year.

 

Thank you very much in advance!

 

Best,

Jose

 

icon

Best answer by francois 3 August 2022, 18:27

View original

4 replies

Nathan
Rank
Userlevel 6
Badge +11

Hi Jose,

This looks like an issue of type of your properties/metric.

Can you check & tell us:

  • the dimension used in your metric where you type the formula ?
  • the type of the 'Requests MRR BE'.Month property

You can check that in the formula bar by hovering the mouse on them

francois
Rank
Userlevel 6
Badge +14

Hi Jose!
I’d bet on the Month in your metric structure might no being the same as the one in your transaction.

Can you try opening your formula in a formula playground set to automatic structure? This should give you the right results. If this works, you can either create a new metric from there or have a look at your different Month dimensions.

I hope this helps you!
François

François (Pigment)
J
Rank
Userlevel 2
Badge +1

Hi Francois!

Thanks for the reply! It worked from the formula playground:

However, I still do not understand why it worked from the formula playground and not directly from the metric?

 

Also what do you mean by “on the Month in your metric structure might no being the same as the one in your transaction”, I do not understand how could they be different? its a date dimension (Dimension (Month) property from List "Requests MRR BE")

 

francois
Rank
Userlevel 6
Badge +14

I think you might have two different calendars, e.g. one in your application and one in a shared app such as a hub. Here’s an example on an application I have. You can see the difference as one is shared (double disks next to the block’s name, name of the source application below) and one is not.

 

François (Pigment)

Reply


Ask your question here!

Terms & ConditionsCookie settings