Level Aware Aggregate Calculation

Question & Answer
1

Hi All,

Can anyone help me recalculate the total units that replaces Internal Total Units - that remains constant for a chosen Period that reflects in the column Granularity and across all L1 deepdive categories?

In the picture below, I’d want the Internal Total units to reflect the same number across 1 and 2 for Apr 1, 2020 and the same number for Jul 1, 2020 and so on and so forth…

image
image510×525 38.2 KB

Please tell me if there is an easy way to create a calculated field that will help me.

Thanks
Tanisha

2

Hi @Tanisha_Shetty -

You can solve this by using a Level Aware Calculation

If you don’t need subtotals/totals the easiest approach would be to use a LAC-A. If you need subtotals/totals then you’ll want to use a LAC-W.

It wasn’t clear to me how you’d wanted to handle the aggregation. Here is an example of a sum across all categories.

sumOver({Total Units},[Dt], PRE_AGG)

image
image936×687 41.2 KB

3

Thanks for the reply Rob. This is exactly what I’m trying to achieve but my Total Units here is a calculated field with an if condition and my date field also appears as Date/ Week/Month/Quarter/Year basis user input. I tried to use the sumover function but it gives me an error.

image
image1230×239 7.06 KB

Would you know how I can deal with it in this case?

Many Thanks!

Tanisha

4

Hi @Tanisha_Shetty -

Can you show the expression for {ChosenTotalUnits} ? It’s most likely an aggregate field and you’ll have to use an additional LAC-W. Similar to this post: Trouble With "Custom" Calculated Field Type Within Analysis - #3 by connoranderson

5

Hi ROb,

Please find the formula for Chosen Total Units -
ifelse(${ChosenType}=‘Internal’,{Internal_total_units},{External_total_units})

This is dependant on the parameter driven field ChosenType.

Thanks
Tanisha

6

{Internal_total_units} & {External_total_units} are calculated fields with aggregation functions right? They have sum() functions in the expression? You can remove the aggregation and just use the columns. The sumOver will perform the sum aggregation.

ifelse(${ChosenType}=‘Internal’,{Internal_units},{External_units})
7

Hi Rob,
I am calculating the Internal_total_units via a sumif function:
sumIf({total_units},type = ‘Internal’). Do you think I should change it to an if condition and calculate it without the aggregation?

Thanks
Tanisha

8

You have it right. If you do that your sumOver should work.

1 Like
9

Thanks Rob! This worked perfectly! :slight_smile:

1 Like