topic Re: Sum of Expression values in App Development

Sum of Expression values

Anonymous — Mon, 22 Jan 2018 16:20:00 GMT

Hello Guys , i want to add below expression and create new column as Summation. Kindly let me know how to do ?

("AverageHrs"/30)*0.8)

("AverageHrs"/30)

(("AverageHrs"/30)*1.2))

I want to add values of all three expression and assign it new field as Summation

I tried like this but its not working

Sum((("Average Flow3Hrs"/30)*0.8)and ("Average Flow3Hrs"/30) and(("Average Flow3Hrs"/30)*1.2))

as summation,

Thanks,

ravi

Re: Sum of Expression values

YoussefBelloum — Mon, 22 Jan 2018 16:34:38 GMT

Hi,

if you want to sum on the script, you should use Group by, example:

Table:

load

field1,

field2,

"Average Flow3Hrs",

Sum((("Average Flow3Hrs"/30)*0.8)+("Average Flow3Hrs"/30) +(("Average Flow3Hrs"/30)*1.2))

as Summation

from...

Group by

field1,

field2,

"Average Flow3Hrs";

Re: Sum of Expression values

sunny_talwar — Mon, 22 Jan 2018 17:00:14 GMT

are you may be looking for this

(("Average Flow3Hrs"/30)*0.8) + ("Average Flow3Hrs"/30) + ("Average Flow3Hrs"/30)*1.2))

as summation,

Re: Sum of Expression values

Anonymous — Mon, 22 Jan 2018 20:36:59 GMT

Thanks Youssef

Re: Sum of Expression values

Anonymous — Mon, 22 Jan 2018 20:38:06 GMT

Thanks sunny , I am still working on it . shall keep posted

Re: Sum of Expression values

Anonymous — Tue, 23 Jan 2018 10:51:35 GMT

hey Sunny

Kindly see my expression it does not give me desired result .

((("Flow3 Hrs per")/(("Average Flow3Hrs"/30)*0.8))+

(("Flow3 Hrs per")/ (("Average Flow3Hrs"/30)))

+ (("Flow3 Hrs per")/(("Average Flow3Hrs"/30)*1.2))) as impactfor3

I am not able to locate impactfor3

Thanks,

Ravi

Re: Sum of Expression values

sunny_talwar — Tue, 23 Jan 2018 12:06:01 GMT

What does it give and what did you hope it would give

Re: Sum of Expression values

Anonymous — Tue, 23 Jan 2018 12:23:10 GMT

Hello Sunny ,

please find details below

Flow3 Hrs per" has a value of 50 , 500, 400

Average Flow3Hrs has value of 154

((("50")/(("Average Flow3Hrs"/30)*0.8))+

(("500")/ (("Average Flow3Hrs"/30)))

+ (("400")/(("Average Flow3Hrs"/30)*1.2))) as impactfor3

impactfor3 should give me addition of all these 3 expression .

Re: Sum of Expression values

sunny_talwar — Tue, 23 Jan 2018 12:29:36 GMT

What is your complete script here? It seems you need some kind of Aggregation here in a resident load to calculate the Average and then left join into your main table. and then finally you will need to use a resident load to do this calculation

Re: Sum of Expression values

Anonymous — Tue, 23 Jan 2018 12:37:24 GMT

[Project1]:

LOAD

"Product Line",

"Flow3 Hrs per",

"Average Flow3Hrs",

(("Average Flow3Hrs"/30)*0.8) + ("Average Flow3Hrs"/30) + ((("Average Flow3Hrs"/30)*1.2))

as flow3summation,

(("Flow3 Hrs per")/(("Average Flow3Hrs"/30)*0.8))+(("Flow3 Hrs per")/(("Average Flow3Hrs"/30)))+(("Flow3 Hrs per")/(("Average Flow3Hrs"/30)*1.2)) as impactfor3

FROM [lib://AttachedFiles/Complexity_Mix.xlsx]

(ooxml, embedded labels, header is 1 lines, table is Model)

where "Product Line"='Terminals';

left Join ([Project1])

[Duration]:

Load

"Product Line",

"Average Flow3Hrs",

"Flow3 Hrs per",

((("Flow3 Hrs per")/(("Average Flow3Hrs"/30)*0.8))+

(("Flow3 Hrs per")/ (("Average Flow3Hrs"/30)))

+ (("Flow3 Hrs per")/(("Average Flow3Hrs"/30)*1.2))) as impactfor3

Resident [Project1]

Group by "Product Line";

Re: Sum of Expression values

sunny_talwar — Tue, 23 Jan 2018 12:41:27 GMT

Average Flow3Hrs comes as a field? Oh so then left join is not needed. Can you explain how you are getting 50, 500, 400 for the same field (Flow3 Hrs per) in a single row? A single row can have only one of the three values, right? Either 50 or 500 or 400... what is the logic for having different numbers for them?

Re: Sum of Expression values

Anonymous — Tue, 23 Jan 2018 12:50:31 GMT

Hey sunny ,

Please find the file attached .

Re: Sum of Expression values

sunny_talwar — Tue, 23 Jan 2018 12:53:33 GMT

Okay this make a lot more sense, now how do you know if you need to multiply by 0.8, 1 or 1.2? Is this based on Complexity level?

Re: Sum of Expression values

Anonymous — Tue, 23 Jan 2018 13:05:11 GMT

yes yes thats assumptions and its fixed values.

I have attached another excel file which has one more set of product line .

So how do i sum all Duration month and Impact month for both product line .

At the end i need only two values

Duration month and Impact month to plot a line chart

Using For loop ?

Re: Sum of Expression values

Anonymous — Tue, 23 Jan 2018 13:06:58 GMT

Sorry here is attachment

Re: Sum of Expression values

sunny_talwar — Tue, 23 Jan 2018 13:16:05 GMT

Try this script

Table:

LOAD *,

[Average Flow3Hrs] * Pick([Complexity Level], 0.8, 1, 1.2)/30 as [Duration (Month)],

[Flow3 Hrs per]/([Average Flow3Hrs] * Pick([Complexity Level], 0.8, 1, 1.2)/30) as [Impact month];

LOAD * INLINE [

Product Line, Complexity Level, Complexity, Projects Per Month, Flow3 Hrs per, Average Flow3Hrs

Terminals, 1, 0.5, 1.25, 50, 154

Terminals, 2, 0.3, 0.75, 500, 154

Terminals, 3, 0.2, 0.5, 400, 154

MP, 1, 0.5, 3.6, 100, 189

MP, 2, 0.3, 2.16, 250, 189

MP, 3, 0.2, 1.44, 400, 189

];

And then you can sum the Duration and Impact month on the front end, unless you need them in the script?

Re: Sum of Expression values

Anonymous — Tue, 23 Jan 2018 14:27:30 GMT

Thanks sunny will try it

Re: Sum of Expression values

Anonymous — Tue, 23 Jan 2018 14:30:41 GMT

(IF(("Complexity Level"='1'),("Flow3 Hrs per")/((("Average Flow3Hrs"/30)*0.8))))+

(IF(("Complexity Level"='2'),("Flow3 Hrs per")/ (("Average Flow3Hrs"/30))))+

(IF(("Complexity Level"='3'),("Flow3 Hrs per")/(("Average Flow3Hrs"/30)*1.2))) as "Total1",

this individual expression works in the script , but if i have to add all three expressions it does not work .

is there any syntax ? specific

Re: Sum of Expression values

sunny_talwar — Tue, 23 Jan 2018 14:33:14 GMT

You don't want to see the individual rows? Only see Aggregated data? Product and Final number will be the only two fields which go into your dashboard?

Re: Sum of Expression values

Anonymous — Wed, 24 Jan 2018 22:22:41 GMT

thanks sunny yes you are right ! It worked !

With the reference to the above table which I have already attached in spreadsheet .

I need to read each and every record of this table and fetch (Duration Month) and (Impact Month Value )

I have written a code kindly check the script and please do see comments .

[Project1]:
LOAD
      "Product Line",
    "Complexity Level",
    Complexity,
    "Projects Per Month",
    "Flow3 Hrs per",
    "Average Flow3Hrs",
     "Duration (Month)",
    "Impact month"

FROM [lib://APtiv documnets/Complexity_Mix_2.xlsx]
(ooxml, embedded labels, header is 1 lines, table is Model);

let vDATA_COUNT = NoOfRows('Project1');
Let vProductlines=Peek('Product Line',Project1,$(n));
let vcomplexity=Peek('Complexity Level',Project1,$(n));
Let vDurationMonthFlow3=Peek("Duration (Month)",,Project1,$(n));
let vImpactmonthFlow3=Peek('Impact month',Project1,$(n));

For n= 1 to $(vDATA_COUNT)-1 // Should read all the rows of the table . Is it correct ?

    For i=1 to 36   // Need Total months as 36

     For x=1 to $(vDurationMonthFlow3)   // Should read through all record values of ("Duration (Month)"

     if $(i) =1 then

         test:
          load
          $(i) as Month,
          $(x) as Month2,
          $(vFlow3Impact_Month) as value   // Should read all record values of 'Impact month'
          AutoGenerate 1;

     ELSE
      test:
          load
          $(i) as Month,
          $(x)+($(i)-1) as Month2,
            $(vFlow3Impact_Month) as value   // Should read all record values of 'Impact month'

          AutoGenerate 1;
     ENDIF

     NEXT x;

Next i ;

Next n;

This code is perfect to my requirements . Its just That it needs some correction specially For Loop

Finally I need only Value and Month2 to draw my Line curve .

Thanks,

Ravi