How to correctly sum the Order total based on year

Q&A
, ,
2

Hello @NirmalSingh, just to clarify, the output for your 2nd table would only contain 2 columns and 1 value for each, am I correct?

If so, I wonder if this would be better displayed in a KPI rather than a table visual. Either way, it seems your calculated field may need a slight adjustment. I think if we avoid running the sum in the ifelse statement, but rather aggregated the resulting value in the visual, you may achieve a better result. Now, as I am sure you have experienced, you can’t compare max of Effective Date in the if statement and then return an unaggregated Untaxed Amount, but we should be able to bypass that. I will return an option below:

ifelse(
maxOver({Effective Date}, [{Order Name}], PRE_AGG) >= ${FromDate} AND
maxOver({Effective Date}, [{Order Name}], PRE_AGG) <= ${ToDate} AND
maxOver({Commitment Date}, [{Order Name}], PRE_AGG) >= ${FromDate} AND maxOver({Commitment Date}, [{Order Name}], PRE_AGG) <= ${ToDate},
{Untaxed Amount Invoiced},
0 --Could also consider replacing this value with NULL so it is excluded
)

Alternatively, we could wrap this into a sumOver statement:

sumOver(
ifelse(
maxOver({Effective Date}, [{Order Name}], PRE_AGG) >= ${FromDate} AND
maxOver({Effective Date}, [{Order Name}], PRE_AGG) <= ${ToDate} AND
maxOver({Commitment Date}, [{Order Name}], PRE_AGG) >= ${FromDate} AND maxOver({Commitment Date}, [{Order Name}], PRE_AGG) <= ${ToDate},
{Untaxed Amount Invoiced},
NULL
), [], PRE_AGG)

One of these 2 options should get you the expected result. I will mark my response as the solution, but let me know if you run into any errors on implementation. Thank you!

1 Like