4 Replies Latest reply on Mar 8, 2017 8:41 AM by Piero Alessio

# Dynamic SUM or MAX based on FLAG

Dear community,

I am struggeling with the following problem. I have a Data source as described below.

It has KPI names and a Flag which indicates that this KPI is either a Kummulative (= sum of all Monthly Values for the selected date range)

or a non-cummulative value(=get only data for the latest month available in partition).

Data Source

MonthValueKPI NameFLAG
2016055AT
2016066AT
2016077AT
2016088AT
2016099AT
2016055BF
2016066BF
2016077BF
2016088BF
2016099BF

Target outcome

KPI NameCalculated Field
a35
b9

Calculated Field

//But this here does not really work

IF ATTR([FLAG])='Y' THEN

(IF (ATTR({FIXED [KPI Name]: (MAX([MONTH]))}))=ATTR([MONTH]) THEN SUM([Value]) ELSE NULL END)

ELSE

SUM([Value])

END

Can you help me solving my problem?

Thank you

• ###### 1. Re: Dynamic SUM or MAX based on FLAG

Hi Piero

I'm not sure if I've intepreted your problem properly, but I've created a solution that gives you your monthly value based on a table calculation, and then uses another table calculation to apply a filter to a specific year

Filtered view gives you

by using a calculated field called FILTER:Month which is set to be discrete

Packaged workbook in v10.1.1 attached.

Hope it helps

Donna

1 of 1 people found this helpful
• ###### 2. Re: Dynamic SUM or MAX based on FLAG

Hi Piero,

All solutionz come in a two-pack;). Find my alternative approach on the solution provided by Donna Coles as reference in attached workbook version 9.3 located in the original thread.

1. Year: mid(str([month]),1,4)

2. Month: str(int(mid(str([month]),5,2)))

3. Date: date("1-"+[Month]+"-"+[Year])

4. Last value: if [Date]={fixed [KPI name]: max([Date])} then [Value] end

5. if attr([flag])="T" then RUNNING_SUM(sum([Value])) ELSEIF  attr([flag])="F" then sum([Last value]) END

Regards,

Norbert

1 of 1 people found this helpful
• ###### 3. Re: Dynamic SUM or MAX based on FLAG

Thank you Norbert Maijoor for showing us an additional method to implement this. Very helpful!

• ###### 4. Re: Dynamic SUM or MAX based on FLAG

Elegant solution, works perfectly! Thank you Donna Coles for this helpful answer!