Unanswered

Summation of rows and difference between two rows

(0) Share

Report

Posted on by luaxs4

Hi - I am trying to create a solution which requires me do summation of column and multiplied by another field and in the next column i need to take the difference between last two entries in the summation column. I have used collection but i am not able to patch in the database. below is the snippet of code used.

Backend is SharePoint. PowerApps is canvas.

Patch(
'Service Level',
Defaults('Service Level'),
{
Title: TxtTitle.Text,
Market: TxtTitleName.Text,
Month: TxtMonth.Text,
WeekDay: TxtDay.Text,
Week: TxtWeekCalc.Text,
ISOWeekNum: TxtISOWeek.Text,
'Client Date TimeZone': DateValue(TxtTodaysDate.Text),
'Client Time Interval': TimeValue(TxtTimeIntervals.Text),
IST: TimeValue(TxtISTCalc.Text),
'Cumalative SLA': Value(TxtCumSLAValue.Value),
'Hourly SLA': Value(TxtHourlySLAValue.Value),
Unasigned: Value(TxtUnassignedVal.Value),
LQTs: Value(TxtLQTVal.Value),
'Slot Status': Value(TxtSlotStatusVal.Value),
'Chat FC': Value(TxtChatFCVal.Value),
'Received Chats': Value(TxtChatRecvdVal.Value),
'FC VS Rec': Value(TxtFCvRecvdVal.Value),
'Required FTE': Value(TxtReqFTEVal.Value),
'Scheduled FTE': Value(TxtSchFTEVal.Value),
'Present FTE': Value(TxtPreFTEVal.Value),
SumProdRecvdCumSLA: Sum(
Filter(
colServiceLevel,
Market = TxtTitleName.Text
),
'Received Chats'
) * Value(TxtCumSLAValue.Value),
RowDifference:Last(FirstN(colServiceLevel,CountRows(colServiceLevel))).SumProdRecvdCumSLA
}
);

Categories:

Building Power Apps

I have the same question (0)

All responses (25)

Answers (0)

WarrenBelz 153,059 Most Valuable Professional on at

Like (0)

Report
Hi @luaxs4 ,

I have parsed it below for ease of reference

Patch( 'Service Level', Defaults('Service Level'), { Title: TxtTitle.Text, Market: TxtTitleName.Text, Month: TxtMonth.Text, WeekDay: TxtDay.Text, Week: TxtWeekCalc.Text, ISOWeekNum: TxtISOWeek.Text, 'Client Date TimeZone': DateValue(TxtTodaysDate.Text), 'Client Time Interval': TimeValue(TxtTimeIntervals.Text), IST: TimeValue(TxtISTCalc.Text), 'Cumalative SLA': Value(TxtCumSLAValue.Value), 'Hourly SLA': Value(TxtHourlySLAValue.Value), Unasigned: Value(TxtUnassignedVal.Value), LQTs: Value(TxtLQTVal.Value), 'Slot Status': Value(TxtSlotStatusVal.Value), 'Chat FC': Value(TxtChatFCVal.Value), 'Received Chats': Value(TxtChatRecvdVal.Value), 'FC VS Rec': Value(TxtFCvRecvdVal.Value), 'Required FTE': Value(TxtReqFTEVal.Value), 'Scheduled FTE': Value(TxtSchFTEVal.Value), 'Present FTE': Value(TxtPreFTEVal.Value), SumProdRecvdCumSLA: Sum( Filter( colServiceLevel, Market = TxtTitleName.Text ), 'Received Chats' ) * Value(TxtCumSLAValue.Value), RowDifference: Last( FirstN( colServiceLevel, CountRows(colServiceLevel) ) ).SumProdRecvdCumSLA } );

but what exactly do you mean by this ? i need to take the difference between last two entries in the summation column
If this is the difference in value in this field between the last two records

RowDifference: Last(colServiceLevel).SumProdRecvdCumSLA - Index( colServiceLevel, CountRows(colServiceLevel) - 1 ).SumProdRecvdCumSLA

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

MVP (Business Applications) Visit my blog Practical Power Apps

Was this reply helpful? Yes No
luaxs4 58 on at

Like (0)

Report

Hi - I have attached a copy of screen shot for you to understand. The data is in excel spreadsheet for you to understand. Consider each row as new entry in SharePoint List.
1. Sumprod column is Summation of all the value in the SumProd (SumProdRecvdCumSLA is the column name in SharePoint) multiplied by Cumlative SLA. Refer 1st part of screen shot.
2. RowDifference column basically difference between Current Entry as against the Last entry made prior. As an how entry is done, it will subtract from the previous one.That is why i mentioned, i need to calculate the difference between two entries in the summation column (SumProd)
The patching needs to be done all at once. Currently its not working for me. If i user Source directly i cant do summation of rows as it has delegable limit and dataset is going to be huge. Hence the collection approach.
Please help

Queries.xlsx

Was this reply helpful? Yes No
luaxs4 58 on at

Like (0)

Report

I tried using your formula but it is not capturing the actual value, shows 0 as outcome in sharePoint

Queries.xlsx

Was this reply helpful? Yes No
luaxs4 58 on at

Like (0)

Report

I tried your approach, but the outcome is 0 in SharePoint. RowDifference: Last(colServiceLevel).SumProdRecvdCumSLA - Index( colServiceLevel, CountRows(colServiceLevel) - 1 ).SumProdRecvdCumSLA

Was this reply helpful? Yes No
WarrenBelz 153,059 Most Valuable Professional on at

Like (0)

Report

@luaxs4 ,

The formula is correct if you want to use the last two entries - see my test below with a list of PCs and prices.

and assuming your entries are in the collection in the same order as they are entered, it should work.

Was this reply helpful? Yes No
luaxs4 58 on at

Like (0)

Report

Hi - Please help me understand when you say entries in the collection are in the same order as they are entered?
What i have done is at the onstart of App, declared the collection as ClearCollect(colServiceLevel, 'Service Level'), and then use patch using the source directly. And only when i am using these two columns i am using collections.

Was this reply helpful? Yes No
WarrenBelz 153,059 Most Valuable Professional on at

Like (0)

Report
Hi @luaxs4 ,

What I mean if you put your collection in a Gallery (Items of colServiceLevel) and a Label with the Text of

ThisItem.SumProdRecvdCumSLA

do you see the two values you want at the bottom ?

Was this reply helpful? Yes No
luaxs4 58 on at

Like (0)

Report

It gives 0 as value.

Was this reply helpful? Yes No
WarrenBelz 153,059 Most Valuable Professional on at

Like (0)

Report

@luaxs4 ,

But that is the value you posted that want to total in both rows, so why is it zero ?

Was this reply helpful? Yes No
luaxs4 58 on at

Like (0)

Report

Wish i knew the solution, why it is Zero. This entire project hinges on success of the data getting correctly captured. Is there any alternate solution, i have literally typed entire code. Can you help?

Was this reply helpful? Yes No