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

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 

Answers

  • 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

  • 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/ 

     

    Blueprint view.JPGGrid view.JPG

     

  • 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. 

    Capture.PNGCapture.PNG
    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

  • 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. 

    Capture.PNGCapture.PNG
    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

  • 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

  • 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. 

  • Hi,

     

    Please try the below logic,

     

    Photo1.pngPhoto1.png

     

    ~Vignesh

  • 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

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

     

    @aabalos , try this way

    Photo1.pngPhoto1.png

     

    ~Vignesh

     

  • Hi Veignesh, 

     

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

    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.