Skip to main content
Solved

Aggregating by sum for Months

  • August 3, 2022
  • 4 replies
  • 217 views
J
Master Trendsetter
Forum|alt.badge.img+2
  • JoseMaster Trendsetter
  • Master Trendsetter
  • 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

 

Best answer by francois

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

4 replies

Nathan
Employee
Forum|alt.badge.img+12
  • NathanEmployee
  • Employee
  • 227 replies
  • August 3, 2022

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
Employee
Forum|alt.badge.img+13
  • francoisEmployee
  • Employee
  • 171 replies
  • Answer
  • August 3, 2022

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
Master Trendsetter
Forum|alt.badge.img+2
  • JoseMaster Trendsetter
  • Author
  • Master Trendsetter
  • 22 replies
  • August 3, 2022

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
Employee
Forum|alt.badge.img+13
  • francoisEmployee
  • Employee
  • 171 replies
  • August 3, 2022

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)


Ask your question here!

Terms & ConditionsCookie settingsAccessibility statement