How to sum a column by looking up a dimension in the module

aabalos

Hi, 

 

I have this module that has a dimension list called Sales Title and a list called Quota Types. I have a line item called SSA Revenue, and it is 1 if it has SSA Revenue, else 0. I have a second line item and I want to sum the total number, grouped by Sales Title. Picture attached. Would I be able to get some help please? 🙂

 

Thanks!

Amanda 

kavinkumar

HI Amanda,

 

Use Select function to get the total. For ex. SSA Revenue Counter[select:Sales Title.Total] where it will return the total of the Sales title list.

 

Hope this helps 🙂

 

Thanks,

Kavin

AlejandroGomez

Hi Amanda,

 

try to do the following:

 

1) create a new Line Item (LI) in your module formatted as number.

2) make sure that the summary method for this new LI is SUM

2) change the dimensionality of that LI (applies to field) and select only the list "Sales Title" 

3) I assume that you already have a LI formatted as List: Sales Title in your current module

4) In the new line item, select the SSA Counter Li and sum it by the LI with Sales Title List format. 

5) now you would be able to see the sum per Sales Title

6) Make sure that both lists you are using as dimensions of your module have a built-in top member (you can easily check and amend this at the configuration tab of the list)

 

See the pictures attached for ruther details (List with Items named "Sx" is your Sales Titles and the lsit with items named "Qx" is your Quota Type list)

 

Cheers,

 

Alex.

 

More knowledge at https://olivehorse.com/ 

 

 

aabalos

Hi Alejandro, 

 

Thank you! Your solution is the exact solution im looking for! However, when I do this in my module, it returns the sum of the whole line item instead of the sum of each sales title, see picture attached. 

In this module, the original pivot is Sales Title as a page, Quota Types as row, and line items as columns. In this current view, i have repivoted it to Sales Title and Quota Types as row and line items as columns. I'm not sure if that is making any difference. Would you know how to fix this?

 

Thanks!

Amanda

aabalos

Hi @AlejandroGomez, 

 

Thank you! Your solution is the exact solution im looking for! However, when I do this in my module, it returns the sum of the whole line item instead of the sum of each sales title, see picture attached. 

In this module, the original pivot is Sales Title as a page, Quota Types as row, and line items as columns. In this current view, i have repivoted it to Sales Title and Quota Types as row and line items as columns. I'm not sure if that is making any difference. Would you know how to fix this?

 

Thanks!

Amanda

Rashmih

Hi ,

@aabalos

 

Could you share the details of line item "Sales Title". . Is there a top level for the list "Quota Types" here...

 

Thanks and regards,

Rashmi

AlejandroGomez

I agree, the issue could be that the list being used in the module do not have a build-in top member item. @aabalos  plese make sure of that at the configuration tap of the list. 

VIGNESH.M

Hi,

 

Please try the below logic,

 

 

~Vignesh

DavidSmith

It is best practice to split SUMs and LOOKUPs, as well as avoiding subsidiary views

 

Create a separate module to do the SUMs and then have the LOOKUP back in the target module

 

This is much more efficient from a calculation perspective

David

VIGNESH.M

Yes David, I totally agree with your suggestion.Its best practiceto do sum in seperate line item.

 

@aabalos , try this way

 

~Vignesh

 

aabalos

Hi Veignesh, 

 

I tried this method and I'm still getting the total sum of the line item. 

Quota Types list does have a top level item. Is it because in my module, the quota types are specific to the sales title? It doesn't list all the Quota Types in the Quota Types list, it just shows the quota types that the sales title holds.