Group by - Canvas App

[AVTS]

Team i am using datatable to display the results. Below formula is used.

With({TheTable: Filter(Dailyinventory, DateDiff(createdon, Today(),Days) =0)},
AddColumns(AddColumns(GroupBy(TheTable,"crf99_sno","crf99_materialdesc", "crf99_materialno", "NewGroup"),
"Created On", Last(NewGroup).createdon,
"Qty",Sum(NewGroup,crf99_qty),
"Sapstock",LookUp(sapstock,material=crf99_materialno,unrestrictedStock)),
"Diff", Sapstock - Qty
))

Current result,

Expected Results,

I need the sum  Qty by Material no.

How to acheieve this.

[gabibalaban]

@AVTS ,

Your formula should work. The Qty column should contains the sum by Material No.

Do you receive an error ? What is the problem ?

[AVTS]

Formula result is shows in topic with current result.

Material is qty shows individualy instead of sum the material no qty .

[RandyHayes · Accepted solution]

@AVTS 

Your results are correct based on your formula.  Your expected results will not happen with that formula because you are grouping on sno, material desc and materialnumber.

sno is your issue.

Please consider changing your Formula to the following:

With({TheTable: 
    Filter(Dailyinventory, DateDiff(createdon, Today(),Days) =0)},
    AddColumns(
        AddColumns(
            GroupBy(TheTable,
               "crf99_materialdesc", "crf99_materialno", "NewGroup"
            ),
            "Created On", Last(NewGroup).createdon,
            "Qty", Sum(NewGroup, crf99_qty),
            "Sapstock", LookUp(sapstock, material=crf99_materialno, unrestrictedStock)
        ),
        "Diff", Sapstock - Qty
    )
)

I hope this is helpful for you.

[AVTS]

Thank you it worked.