Summary report

[PROMAN]

Hi all!

I created a template to generate a PDF file, as below:

<<Start: OrderBy(Select(SliceXXX[KeyColumn], TRUE), [Code], FALSE)>>

<<[Code]>>

<>

With this, data like the following example are generated:

1012
1012
1012
1013
1013
1013
1013
1019
1019

But I need a summary table, as follows:

1012 = 3
1013 = 4
1019 = 2

Thanks for the help.

[1minManager]

Create a virtual column with this formula and called "COUNT"

IF(
TOP(Select(TableName[_RowNumber],[Code]=[_ThisRow].[Code]))=[_ThisRow].[_RowNumber],
SUM(Select(TableName[Code],[Code]=[_ThisRow].[Code])),
0)

This will count the number of other rows with the same [CODE] but only put the sum total in the row with the highest [_RowNumber]. Since we only want to pull in one row per [CODE]

Then change your start condition to be

<<Start: OrderBy(Select(SliceXXX[KeyColumn], [COUNT]>0), [Code], FALSE)>>

Simon@1minManager.com

[PROMAN]

Hello Simon,
Thank you very much for your help.
I changed the formula and created the virtual column.
But I think I’m doing something wrong. The expression assistant displays the following error: “TOP has invalid inputs”

[Steve]

Use INDEX() rather than TOP().

[PROMAN]

Hello Steve. Thanks for your help. I will test this function. Thank you

[1minManager]

Sorry, 2 errors. I think it should be

IF(
MAX(Select(TableName[_RowNumber],[Code]=[_ThisRow].[Code]))=[_ThisRow].[_RowNumber],
COUNT(Select(TableName[Code],[Code]=[_ThisRow].[Code])),
0)

So in English the Formula is saying:

"Make a list of all the rows which the same CODE as this row. If this row has the highest row number out of that list then Count how many rows with this code there are (including this row) and put that number here. If this row hasn’t got the highest row number then the number is 0"

Hope this helps

Simon@1minManager.com

[PROMAN]

Hello Simon,

Thank you very much for your support.

Now it’s working. The number of rows with the same code is correct.

But the number of rows is repeating, as below:

0948 - 3
0948 - 3
0948 - 3
0949 - 1
0964 - 4
0964 - 4
0964 - 4
0964 - 4
0967 - 2
0967 - 2

What I have:

Virtual colum:
IF(
MAX(Select(TableName[_RowNumber],[Code]=[_ThisRow].[Code]))=[_ThisRow].[_RowNumber],
COUNT(Select(TableName[Code],[Code]=[_ThisRow].[Code])),
0)

Template report:
<<Start: OrderBy(Select(SliceXXX[KeyColumn], [COUNT]>0), [Code], FALSE)>>
<<[Code]>> - <<[COUNT]>>
<<.End>>

I tried to make some changes without success.

I believe that the total count is being included in all the rows, when it should be in only one, with “0” for the others.

[Steve]

Ideally, you’d have a separate table where the Code values occur only once each, then you could use that with your Start expression. The solutions @1minManager has suggested are all extremely inefficient, but unfortunately the only way to approach the problem without adding tables.

[PROMAN]

Hello Steve, thank you.

Could you tell me a bit about getting the result by adding tables?

[Ray]

Hi Steve - could you explain the concept of creating separate tables for summary data? Or is there a simper solution that has been developed recently?

I need to summarize several fields -

I have a Products table and an Order Details table. The fields in my order details table that I need to track are:
ProductName - product that is collected
Quantity collected - number of items (ProductName) that are collected
Weight - weight of items collected (total - one value for each record)

I want to summarize the:
total number of items (Quantity Collected) for each Product
total weight of items for each Product

So my report might look similar to this:

Product Collected Weight
BM8 13 18.5
PW23 10 46.3
etc.

Any help for an efficient solution would be appreciated .

Thanks
Ray

[Steve]

It’s quite simple, actually. Create a new table for your summary report with virtual columns corresponding to the Product table columns you indicated: Product Name, Quantity collected, and Weight. Give each an App formula expression that computes the desired total.

[1minManager · Accepted solution]

Try changing

PROMAN:

MAX(Select(TableName[_RowNumber],[Code]=[_ThisRow].[Code]))=[_ThisRow].[_RowNumber],

to

MAX(Select(TableName[_RowNumber],[Code]=[_ThisRow].[Code]))=[_RowNumber],

[PROMAN]

Worked perfectly. Great job!!!
Thank you very much.

[Ray]

Thanks Steve - I have a report that works now.

I wanted to make one adjustment to the App Formulas - to limit the display according to a column in the Order Details table. I have a TRUE/FALSE column called Collected. I want to limit the display to those records that are FALSE. My app formula is:

SUM(SELECT(Order Details[QtyCollected], ([ProductID] = [_THISROW].[ProductID]), FALSE))

I’ve tried different variations using AND to select only rows where collected is FALSE but have not been able to create a valid expression.

Cheers
Ray

[Steve]

Try:

SUM(
  SELECT(
    Order Details[QtyCollected],
    AND(
      ([ProductID] = [_THISROW].[ProductID]),
      NOT([Collected])
    ),
    FALSE
  )
)

See also:

help.appsheet.com

AND()

Are all true?

help.appsheet.com

NOT()

Is it false?

[Ray]

Hi Steve - thank you. This is working perfectly. Yes the NOT argument checks to see if [Collected] is FALSE (NOT TRUE)

And the last FALSE statement allows for duplicates - correct?

Very helpful - thanks again.

Ray

[Steve]

Ray:

And the last FALSE statement allows for duplicates - correct?

Yep: SELECT(..., ..., FALSE) explicitly tells SELECT() to provide the duplicate values, too. Putting FALSE in there is technically not needed, as including duplicates is the default behavior anyway.