Grouping Issue

[Sirfyaad]

I have created a Bot that works correctly that saves on the drive a pdf single receipts of the receipts table. Here is the expression of the virtual column. CONCATENATE([DATE],[channel],[TEAM NAME],[TOTAL PHOTO])

Here is the expression of the table Slice:

[ENTRY CODE]= MINROW(“Photos”, “_ROWNUMBER”, [DataPerformChannel]=[_THISROW].[DataPerformChannel])

PDF is getting generated in some way like this.

"GroupForReports" Slice table 
1. 06/05/2023, RINTU, FLIPKART,100
2. 06/05/2023, PANKAJ,FLIPKART,100
3. 06/05/2023, RINTU, FLIPKART,50
4. 06/05/2023, PANKAJ,AMAZON,115

The desired result should look like this:

"GroupForReports" Slice table 
1. 06/05/2023, RINTU,FLIPKART,150
2. 06/05/2023, PANKAJ,FLIPKART,100
3. 06/05/2023, PANKAJ,AMAZON,115

I have tried with this code written in a Google doc Template

<<START:FILTER(GroupForReports, [DATE] = TODAY())>><<[DATE]>>
<<[TEAM NAME]>>
<<[CHANNEL]>>
<<[TOTAL PHOTO]>><<END>>

It simply sorts but does not group

[Fael]

First, you need to fix your VC expression so it sums the [TOTAL PHOTOS] that have the same [channel]:

CONCATENATE([DATE],[channel],[TEAM NAME],
    SUM(SELECT(
        TABLE_NAME[TOTAL PHOTO],
        AND(
            [channel] = [_THISROW].[channel],
            [ENTRY CODE] = MINROW(“Photos”, “_ROWNUMBER”, [DataPerformChannel]=[_THISROW].[DataPerformChannel])
        )
    ))
)

Then you need to change your slice expression to only get unique [Chanel] values:

AND(
    [ENTRY CODE]= MINROW(“Photos”, “_ROWNUMBER”, [DataPerformChannel]=[_THISROW].[DataPerformChannel]),
    [_ROWNUMBER] = ANY(ORDERBY(FILTER(
        "TABLE_NAME",
        [channel] = [_THISROW].[channel],
        ),
        [_ROWNUMBER]
        )
    )
)

I'm assuming [_ROWNUMBER] is your key column, in case it is not, you shall change the expression to match your key column. As I don't know your table name, please replace both "TABLE_NAME" with your table name.

[Sirfyaad]

---

@Fael wrote:

[DataPerformChannel]=[_THISROW].[DataPerformChannel]

---

Hi @Fael Showing error here.

[Entry Code] is my Key column, and the [Channel] column is EnumTypes.

Error: Ran out of resources evaluating: SUM(SELECT(Photos[TOTAL QTY],AND(([CHANNEL] = [_THISROW].[CHANNEL]), ([ENTRY CODE] = ANY(SELECT(Photos[ENTRY CODE],AND(([DataPerformChannel] = [_THISROW].[DataPerformChannel]), ([_ROWNUMBER] = MIN(SELECT(Photos[_RowNumber],([DataPerformChannel] = [_THISROW].[DataPerformChannel])))))))))))

After test Expression

[Fael]

I didn't write this part of the code by myself. I copied and pasted from your slice expression:

---

@Sirfyaad wrote:

Here is the expression of the table Slice:

---

[ENTRY CODE]= MINROW(“Photos”, “_ROWNUMBER”, [DataPerformChannel]=[_THISROW].[DataPerformChannel])

What is it supposed to do? Does the expression work if you remove this AND condition? It may have better ways of doing what you want.

[Sirfyaad]

Hi @Fael Both your formulas are correct, but when I am testing the expression, it is showing error. Please tell me how can I fix this error.

The image of the table is below. 

 

[Sirfyaad]

---

@Fael wrote:

[DataPerformChannel]=[_THISROW].[DataPerformChannel])

---

Hi @Fael [DataPerformChannel] is a virtual column. Inside which I have used this expression. It is showing green tick below the expression. And when I am checking expression Test  it is showing error.

CONCATENATE([DATE],[channel],[TEAM NAME],
    SUM(SELECT(
        TABLE_NAME[TOTAL PHOTO],
        AND(
            [channel] = [_THISROW].[channel],
            [ENTRY CODE] = MINROW(“Photos”, “_ROWNUMBER”, [DataPerformChannel]=[_THISROW].[DataPerformChannel])
        )
    ))
)

[Fael]

I got it. The problem here is that you cannot call a column's value within the column itself. You cannot use the DataPerformChannel value to calculate the DataPerformChannel value.

Again, I need to know what the point of this expression is. Why do you need to check the [ENTRY CODE]?

You could just remove this line if the [ENTRY CODE] is not important:

CONCATENATE([DATE],[channel],[TEAM NAME],
    SUM(SELECT(
        TABLE_NAME[TOTAL PHOTO],
        [channel] = [_THISROW].[channel]
    ))
)

[Sirfyaad]

---

@Fael wrote:

Again, I need to know what the point of this expression is

First of all I thank you. to help. @Fael 

I want that if [team name] and [channel] are equal, then their total value should be calculated. So that I do not have any problem in SUM in the template. 

I want to do grouping on [team name] and [channel] wise. If both are equal. in current date. 

When I am making more than one entry with the name of a team, only one entry of that name is showing in the template. I have 10 teams, the template is showing only 10 entries. Not showing in group. 

[Fael]

---

@Sirfyaad wrote:

I want that if [team name] and [channel] are equal, then their total value should be calculated. So that I do not have any problem in SUM in the template. 

I want to do grouping on [team name] and [channel] wise. If both are equal. in current date. 

---

[team name] is an EnumList, so you can select more than one option. In order to it "be equal" does it shall have all the same selected options or a common option only is enough? Besides that, just [date] from today should be shown and sum? Assuming a common option only is enough and only today [date], then: 

CONCATENATE([DATE],[channel],[TEAM NAME],
    SUM(SELECT(
        Photos[TOTAL PHOTO],
        AND(
            [channel] = [_THISROW].[channel],
            COUNT(INTERSECT([team name], [_THISROW].[team name]))>0,
            [DATE] = TODAY()
        )
    ))
)

Please let me know if this VC expression works for you.

You could just repeat the grouping conditions at the slice:

[_ROWNUMBER] = ANY(ORDERBY(FILTER(
    "Photos",
    AND(
        [channel] = [_THISROW].[channel],
        COUNT(INTERSECT([team name], [_THISROW].[team name]))>0,
        [DATE] = TODAY()
    )
    ),
    [_ROWNUMBER]
    )
)

[Sirfyaad]

 Group sum in template is not happening. 

[Fael]

Could you show me what is happening? The same way you did on your first post. Eg:

[Sirfyaad]

---

@Sirfyaad wrote:

"GroupForReports" Slice table 1. 06/05/2023, RINTU, FLIPKART,100 2. 06/05/2023, PANKAJ,FLIPKART,100 3. 06/05/2023, RINTU, FLIPKART,50 4. 06/05/2023, PANKAJ,AMAZON,115

---

@Fael  I want you to see the difference between the two.

"GroupForReports" Slice table
1. 06/05/2023, RINTU, FLIPKART,100
2. 06/05/2023, PANKAJ,FLIPKART,100
3. 06/05/2023, RINTU, FLIPKART,50
4. 06/05/2023, PANKAJ,AMAZON,115

And I want to create a report in this way. 

"GroupForReports" Slice table
1. 06/05/2023, RINTU,FLIPKART,150
2. 06/05/2023, PANKAJ,FLIPKART,100
3. 06/05/2023, PANKAJ,AMAZON,115

Cannot calculate [total qty] in template. I want to calculate the total value of [team name] and [channel] if they are equal. 

[Fael]

---

@Sirfyaad wrote:

 Group sum in template is not happening. 

---

Are you using my expression in your slice and virtual column? The result is the same as the above? Nothing changes?

[Sirfyaad]

Is. I have done all your expressions to him. In which only the total value is not being calculated.

[Sirfyaad]

---

@Sirfyaad wrote:

<<START:FILTER(GroupForReports, [DATE] = TODAY())>><<[DATE]>> <<[TEAM NAME]>> <<[CHANNEL]>> <<[TOTAL PHOTO]>><<END>>

---

Is this template expression of correct. Because I am using this expression in the template. 

[Fael]

---

@Sirfyaad wrote:

Is this template expression of correct. Because I am using this expression in the template. 

---

Well, it is not. You need to show the [DataPerformChannel ] virtual column we created. Use it instead:

<<START:FILTER(GroupForReports, [DATE] = TODAY())>><<[DataPerformChannel]>><<END>>

[Sirfyaad]

This is the test expression for the virtual column. 

Below I have given the expression test result of slice table. 

 

The column with [Total Qty] is getting generated in this way. The expression of the template is given below.

 

DATE	TEAM NAME	CHANNEL	TOTAL QTY	VIDEO	POD PHOTO	EXTRA PHOTO	HANDOVER TO
<<START:FILTER(GroupForReports, [DATE] = TODAY())>><<[DATE]>>	<<[TEAM NAME]>>	<<[CHANNEL]>>	<<START:FILTER(GroupForReports, [DATE] = TODAY())>><<[DataPerformChannel]>><<END>>	<<[VIDEOS QTY]>>	<<[POD PHOTO QTY]>>	<<[ADDITIONAL PHOTO]>>	<<[HANDOVER TO]>><<END>>

[Sirfyaad]

@Fael Somebody please fix this error. 

[Fael · Accepted solution]

Hi,

You don't need to filter twice. If you want the sum [TOTAL QTY], then you need to change the virtual column:

SUM(SELECT(
    Photos[TOTAL PHOTO],
    AND(
        [channel] = [_THISROW].[channel],
        COUNT(INTERSECT([team name], [_THISROW].[team name]))>0,
        [DATE] = TODAY()
    )
))

Then show it on the table:

DATE	TEAM NAME	CHANNEL	TOTAL QTY	VIDEO	POD PHOTO	EXTRA PHOTO	HANDOVER TO
<<START:FILTER(GroupForReports, [DATE] = TODAY())>><<[DATE]>>	<<[TEAM NAME]>>	<<[CHANNEL]>>	<<[DataPerformChannel]>>	<<[VIDEOS QTY]>>	<<[POD PHOTO QTY]>>	<<[ADDITIONAL PHOTO]>>	<<[HANDOVER TO]>><<END>>

If it is blank, please print me the result of the expression test:

[Sirfyaad]

@Fael Thank you again.

[Sirfyaad]

@Fael One last question. 

How to change the name of pdf. I want the pdf name to be "Returnphoto" and current date.I want that when I download, it should be downloaded with the same name. 

[Fael]

Please refer to:

Create and save a file from an automation - AppSheet Help 

Archive files created from a bot - AppSheet Help