# Qlik Sense App Development

Highlighted
MVP

## Re: Sum of Expression values

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?

Contributor

## Re: Sum of Expression values

Hey sunny ,

Please find the file attached .

MVP

## Re: Sum of Expression values

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?

Contributor

## Re: Sum of Expression values

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 ?

Contributor

## Re: Sum of Expression values

Sorry here is attachment

MVP

## Re: Sum of Expression values

Try this script

Table:

[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];

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?

Contributor

## Re: Sum of Expression values

Thanks sunny will try it

Contributor

## Re: Sum of Expression values

(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

MVP

## Re: Sum of Expression values

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?

Contributor

## Re: Sum of Expression values

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]:
"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:
\$(i) as Month,
\$(x) as Month2,
\$(vFlow3Impact_Month) as value   // Should read all record values of 'Impact month'
AutoGenerate 1;

ELSE
test:
\$(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