Totals at rolled up level

pallavi6.gupta
pallavi6.gupta
in Modeling

Hi,

We have applied some filters on the basis of which we want the data to be published on the dashboards. The data is correctly being picked up as stated in the filters, however at the total level(level at which data is getting rolled up) the totals are coming incorrect , it picks totals of the entire e.g. region/category instead of picking up totals of the filtered items.

Eg. if SKU1 = 15 ;  SKU 2 =10 (both roll up to category 1)

On applying filter i want only SKU 1 i.e. 15 , then at total it should show only 15 rather than 25.

 

Thanks 

Tagged:

Answers

  • usman.zia
    usman.zia

    Hi @pallavi6.gupta 

     

    The filtering method you have described is only a display element it does not calculate the total based on the filtering to do this you would need to create another line item and such as 1 if it is filtered then sum the values based upon this line item to get the total. 

     

    Or you could times the line item by the filter so when it is true then times by 1 within the line item formula, this would also give filtering based calculated totals.

     

    I hope this helps. 

     

    Thanks,

     

    Usman 

  • saurabh.raheja
    saurabh.raheja

    Hi @usman.zia 

     

    Thanks for the prompt response! The 2nd solution given by you is quite simple but this will impact the calculations being performed on the basis of this line item.

    Also, we are creating 2 different dashboards basis the filters. 1 is for Imports and 1 is for Domestic Goods, so we need to show the totals separately as per the filters applied to the 2 views. Does that mean as per the 1st solution for dashboarding purpose, we will need to create 2 line items for Domestic and Import and that will be times the number of line items which are holding values? Further, can you please explain how to sum basis the filter in the new line item, can it not be done by multiplying the filter line item which contains only 1 or 0?

     

    Regards

    Saurabh

  • rob_marshall
    rob_marshall

    @saurabh.raheja ,

     

    Why multiple the data, just reference it with a formula (if filter then data else 0) which assumes your filter is Boolean formatted (which is should be for performance reasons).  Essentially, it is the same as multiplying it, but easier on Hyperblock because it is not having to do the multiplication.  

     

    Thanks,

     

    Rob

  • pallavi6.gupta
    pallavi6.gupta

    Hi Rob,

    Thanks for your solution, we tested and it is working however there are few concerns:

    1. We will have to create 2 line items for every line item that has data (one for import and one for domestic)

    2. Each line item is taking a cell count of 11 million

    Is there any other optimized solution?

     

    Regards,

    Pallavi

  • rob_marshall
    rob_marshall

    @pallavi6.gupta ,

     

    11 million cells per line item is quite a bit, but not unheard of...How many line items?  Can you dimensionalize the module by Domestic and Import to reduce the line items?  This will not decrease the size of the module, but it could clean it up.  Are there line items that have summaries turned on but are not needed?  What is the overall dimensionality of the module?  Do you have unneeded line items in the module that should be in other "SYS" modules (for example, time filtering or code of one of the lists)?  Are you attempting to put too much in one module?  For example, can you have an "input" module as well as a Calculation module where the calculation module is just the filtered data for reporting and is used for the dashboard?

     

    Without knowing more information, there are too many variables.

     

    Thanks,

     

    Rob

Categories