What should be the if expression in the report template

[Pointer]

Hi everyone,

What should be the if expression that I should use in the yellow and green (especially) cells in my report template in order to get the results in the diff line that I am trying to express in the picture. Thanks

@AleksiAlkio @Suvrutt_Gurjar @Marc_Dillon @WillowMobileSys @jaichith 

[Pointer · Accepted solution]

<<IFS(ISBLANK(ANY(SELECT(Report Table[Value], AND([_THISROW-1].[ID] = [ID], [_THISROW-1].[Date] =  [Date])))), "-",

AND(ISBLANK(ANY(SELECT(Report Table[Value], AND([_THISROW-1].[ID] = [ID], [_THISROW-1].[Date] =  [Date])))), 
MAX(SELECT(Report Table[Value], AND([_THISROW-1].[ID] = [ID], [_THISROW-1].[Date] = [Date]))) -MIN(SELECT(Report Table[Value], AND([_THISROW-1].[ID] = [ID],[_THISROW-1].[Date] = [Date])))>5),

SUBSTITUTE(TEXT(MAX(SELECT(Report Table[Value], AND([_THISROW-1].[ID] = [ID],[_THISROW-1].[Date] = [Date])))-MIN(SELECT(Report Table[Value], AND([_THISROW-1].[ID] = [ID],[_THISROW-1].[Date] = [Date])))),".",",")

AND(ISBLANK(ANY(SELECT(Report Table[Value], AND([_THISROW-1].[ID] = [ID], [_THISROW-1].[Date] = [Date])))), 
MAX(SELECT(Report Table[Value], AND([_THISROW-1].[ID] = [ID],[_THISROW-1].[Date] = [Date])))-MIN(SELECT(Report Table[Value], AND([_THISROW-1].[ID] = [ID],[_THISROW-1].[Date] = [Date])))<=5),

SUBSTITUTE(TEXT(MAX(SELECT(Report Table[Value], AND([_THISROW-1].[ID] = [ID], [_THISROW-1].[Date] = [Date])))-MIN(SELECT(Report Table[Value], AND([_THISROW-1].[ID] = [ID],[_THISROW-1].[Date] = [Date]))))".",","))>>

I solved it this way, thanks @Suvrutt_Gurjar @jaichith 

[jaichith]

Please elaborate the condition here

If (condition - what to be checked, if the condition is true , if the condition is false) 

[Pointer]

green cell conditions; (diff row)

<<if: max(value)-min(value)>5>>max(value)-min(value)<<Endif>>

<<if: max(value)-min(value)<=5>>max(value)-min(value)<<Endif>>

<<if: isblank(list(value))>>”-“<<Endif>>

yellow cell conditions; (diff row)

<<if: max(value)>><<(measured)><<Endif>>-<<if: min(value)>><<(measured)><<Endif>><<if: isblank(list(value))>>”-“<<Endif>>

[jaichith]

I can understand your need but. Could you please give me explanation in words. I am asking this because you expression doesn't have proper syntax.

[Pointer]

.I can list related datas but I cant find the suitable expression  on this condition ( I mentioned above )in the difference row.

[Pointer]

<<If: MAX(SELECT(Report Table[Value], AND([_THISROW-1].[ID] = [ID], [_THISROW-1].[Date] = [Date])))-MIN(SELECT(Report Table[Value], AND([_THISROW-1].[ID] = [ID],[_THISROW-1].[Date] = [Date])))>5>><<MAX(SELECT(Report Table[Value], AND([_THISROW-1].[ID] = [ID],[_THISROW-1].[Date] = [Date])))-MIN(SELECT(Report Table[Value], AND([_THISROW-1].[ID] = [ID],[_THISROW-1].[Date] = [Date])))>><<EndIf>>

<<If: MAX(SELECT(Report Table[Value], AND([_THISROW-1].[ID] = [ID],[_THISROW-1].[Date] = [Date])))-MIN(SELECT(Report Table[Value], AND([_THISROW-1].[ID] = [ID],[_THISROW-1].[Date] = [Date])))<=5>><<MAX(SELECT(Report Table[Value], AND([_THISROW-1].[ID] = [ID], [_THISROW-1].[Date] = [Date])))-MIN(SELECT(Report Table[Value], AND([_THISROW-1].[ID] = [ID],[_THISROW-1].[Date] = [Date])))>><<EndIf>>

I solved the difference calculation with this expression, but if there is no value, the result is (0.00). I want it to be "-", how do I ensure it?

[jaichith]

Syntax
IF(logical-expression, value-if-true, value-if-false)

Logical-expression ???,

value-if-true???,

value-if-false ???

https://support.google.com/appsheet/answer/10108198?hl=en

[Pointer]

Can you write me the correct expression, I know all of them, I just want it to be (-) instead of (0.00) if there is no max-min value.

[Suvrutt_Gurjar]

Please try 

<<IFS( 

  MAX(SELECT(Report Table[Value], AND([_THISROW-1].[ID] = [ID], [_THISROW-1].[Date] =         [Date]))) -MIN(SELECT(Report Table[Value], AND([_THISROW-1].[ID] = [ID],[_THISROW-1].[Date] = [Date])))>5,

TEXT(MAX(SELECT(Report Table[Value], AND([_THISROW-1].[ID] = [ID],[_THISROW-1].[Date] = [Date])))-MIN(SELECT(Report Table[Value], AND([_THISROW-1].[ID] = [ID],[_THISROW-1].[Date] = [Date])))),

MAX(SELECT(Report Table[Value], AND([_THISROW-1].[ID] = [ID],[_THISROW-1].[Date] = [Date])))-MIN(SELECT(Report Table[Value], AND([_THISROW-1].[ID] = [ID],[_THISROW-1].[Date] = [Date])))<=5,

TEXT(MAX(SELECT(Report Table[Value], AND([_THISROW-1].[ID] = [ID], [_THISROW-1].[Date] = [Date])))-MIN(SELECT(Report Table[Value], AND([_THISROW-1].[ID] = [ID],[_THISROW-1].[Date] = [Date])))),

ISBLANK(SELECT(Report Table[Value], AND([_THISROW-1].[ID] = [ID], [_THISROW-1].[Date] =         [Date]))),

"-"

)>>

[jaichith]

Sir, I had similar idea but he had came up with using different syntax of  << IF:.....>>

does IFS () work without <<endif>>? 

If () in this documentation looks alike function of IFS()

 

[jaichith]

I think this part of the expression should be at first since it should be evaluated first 

ISBLANK(SELECT(Report Table[Value], AND([_THISROW-1].[ID] = [ID], [_THISROW-1].[Date] =         [Date]))),

"-"

Reason: max () and min () may return 0 if the list is has no value.  IFS() will evaluate condition 1 by 1

[Pointer]

I tried but when the value was blank the result was "0.00" again.  "-" It didn't happen

[Suvrutt_Gurjar]

Please try 

<<IFS( 

ISBLANK(SELECT(Report Table[Value], AND([_THISROW-1].[ID] = [ID], [_THISROW-1].[Date] =         [Date]))),

"-",

 MAX(SELECT(Report Table[Value], AND([_THISROW-1].[ID] = [ID], [_THISROW-1].[Date] =         [Date]))) -MIN(SELECT(Report Table[Value], AND([_THISROW-1].[ID] = [ID],[_THISROW-1].[Date] = [Date])))>5,

TEXT(MAX(SELECT(Report Table[Value], AND([_THISROW-1].[ID] = [ID],[_THISROW-1].[Date] = [Date])))-MIN(SELECT(Report Table[Value], AND([_THISROW-1].[ID] = [ID],[_THISROW-1].[Date] = [Date])))),

MAX(SELECT(Report Table[Value], AND([_THISROW-1].[ID] = [ID],[_THISROW-1].[Date] = [Date])))-MIN(SELECT(Report Table[Value], AND([_THISROW-1].[ID] = [ID],[_THISROW-1].[Date] = [Date])))<=5,

TEXT(MAX(SELECT(Report Table[Value], AND([_THISROW-1].[ID] = [ID], [_THISROW-1].[Date] = [Date])))-MIN(SELECT(Report Table[Value], AND([_THISROW-1].[ID] = [ID],[_THISROW-1].[Date] = [Date]))))

)>>

[Pointer]

unfortunately it's still the same, it hasn't changed

[jaichith]

Share the image of the output

[jaichith]

https://support.google.com/appsheet/answer/11568425?hl=en

[jaichith]

This may help you

 

[Pointer]

I looked at all of them and wrote them because I couldn't find a solution. thanks

[jaichith]

<<Condition 1>> what to return<<endif>> 

<<Condition 2>> what to return<<endif>> 

<<Condition 3>> what to return<<endif>> 

You can add your need in (ie is to return - )  condition 3

Example :

<<If: MAX(SELECT(Report Table[Value], AND([_THISROW-1].[ID] = [ID],[_THISROW-1].[Date] = [Date])))-MIN(SELECT(Report Table[Value], AND([_THISROW-1].[ID] = [ID],[_THISROW-1].[Date] = [Date])))=0>>-<<endif>>

Alter your expression according to your need. Add it to existing expression. If the subtraction value is 0 it will return -

[jaichith]

Note : untested expression.

<<If: MAX(SELECT(Report Table[Value], AND([_THISROW-1].[ID] = [ID],[_THISROW-1].[Date] = [Date])))-MIN(SELECT(Report Table[Value], AND([_THISROW-1].[ID] = [ID],[_THISROW-1].[Date] = [Date])))=0>>-<<endif>>

Alter your expression according to your need. Add it to existing expression. If the subtraction value is 0 it will return -

[Pointer]

I want it to be "-" when there is no max and min value, not when the subtraction is zero (max-min can be zero)

[jaichith]

How do you say there is no max and min value ? 

Because expression will find minimum or maximum value in the given list. 

Do you mean if the field  is  blank ?   isblank () ? 

[Pointer]

Yes when it's blank

If Condition MAX and MIN value ISBLANK([Value]) the result should be "-"

Picture: Sample 3

[jaichith]

Note : untested expression

Use this expression as 1st condition. Other expressions should follow it. I mean first this expression should be evaluated since it is first condition. 

<<If: or(isblank([field1]), is blank([field2]),... )>>-<<endif>>

[Pointer]

<<If: MAX(SELECT(Report Table[Value], AND([_THISROW-1].[ID] = [ID], [_THISROW-1].[Date] = [Date])))-MIN(SELECT(Report Table[Value], AND([_THISROW-1].[ID] = [ID],[_THISROW-1].[Date] = [Date])))>5>><<MAX(SELECT(Report Table[Value], AND([_THISROW-1].[ID] = [ID],[_THISROW-1].[Date] = [Date])))-MIN(SELECT(Report Table[Value], AND([_THISROW-1].[ID] = [ID],[_THISROW-1].[Date] = [Date])))>><<EndIf>>

<<If: MAX(SELECT(Report Table[Value], AND([_THISROW-1].[ID] = [ID],[_THISROW-1].[Date] = [Date])))-MIN(SELECT(Report Table[Value], AND([_THISROW-1].[ID] = [ID],[_THISROW-1].[Date] = [Date])))<=5>><<MAX(SELECT(Report Table[Value], AND([_THISROW-1].[ID] = [ID], [_THISROW-1].[Date] = [Date])))-MIN(SELECT(Report Table[Value], AND([_THISROW-1].[ID] = [ID],[_THISROW-1].[Date] = [Date])))>><<EndIf>>

How should I do this expression?

[jaichith]

Just an example for condition1. 

2nd condition may not be required. 

Isblank(SELECT(Report Table[Value], AND([_THISROW-1].[ID] = [ID], [_THISROW-1].[Date] = [Date])))

If above expression didn't work. Wrap select() with any()

Isblank(any(SELECT(Report Table[Value], AND([_THISROW-1].[ID] = [ID], [_THISROW-1].[Date] = [Date]))))

Construct balance expression and include condition 2, 3 etc one by one

[Pointer]

When I add other conditions the result is;

-
0.00

[jaichith]

Do you mean it returning both - & 0 ?

This I was predicting.

[Pointer]

Yes, after all, there are both.

[jaichith]

Try IFS () as specified by @Suvrutt_Gurjar sir

[Pointer · Accepted solution]

<<IFS(ISBLANK(ANY(SELECT(Report Table[Value], AND([_THISROW-1].[ID] = [ID], [_THISROW-1].[Date] =  [Date])))), "-",

AND(ISBLANK(ANY(SELECT(Report Table[Value], AND([_THISROW-1].[ID] = [ID], [_THISROW-1].[Date] =  [Date])))), 
MAX(SELECT(Report Table[Value], AND([_THISROW-1].[ID] = [ID], [_THISROW-1].[Date] = [Date]))) -MIN(SELECT(Report Table[Value], AND([_THISROW-1].[ID] = [ID],[_THISROW-1].[Date] = [Date])))>5),

SUBSTITUTE(TEXT(MAX(SELECT(Report Table[Value], AND([_THISROW-1].[ID] = [ID],[_THISROW-1].[Date] = [Date])))-MIN(SELECT(Report Table[Value], AND([_THISROW-1].[ID] = [ID],[_THISROW-1].[Date] = [Date])))),".",",")

AND(ISBLANK(ANY(SELECT(Report Table[Value], AND([_THISROW-1].[ID] = [ID], [_THISROW-1].[Date] = [Date])))), 
MAX(SELECT(Report Table[Value], AND([_THISROW-1].[ID] = [ID],[_THISROW-1].[Date] = [Date])))-MIN(SELECT(Report Table[Value], AND([_THISROW-1].[ID] = [ID],[_THISROW-1].[Date] = [Date])))<=5),

SUBSTITUTE(TEXT(MAX(SELECT(Report Table[Value], AND([_THISROW-1].[ID] = [ID], [_THISROW-1].[Date] = [Date])))-MIN(SELECT(Report Table[Value], AND([_THISROW-1].[ID] = [ID],[_THISROW-1].[Date] = [Date]))))".",","))>>

I solved it this way, thanks @Suvrutt_Gurjar @jaichith 

[Suvrutt_Gurjar]

Nice to know that you solved it. Let us also applause @jaichith also who persisted with you through all your queries. I am sure his inputs helped you in crafting the final solution.

[Pointer]

How can I do this formula with if, because it does not meet the bold printing requirement?

[Suvrutt_Gurjar · Accepted solution]

Please try below three sub expressions together. Please highlight the subexpression whose results you wish to highlight in the template as the example below shows. 

<<IFS(ISBLANK(ANY(SELECT(Report Table[Value], AND([_THISROW-1].[ID] = [ID], [_THISROW-1].[Date] = [Date])))), "-")>>

<<IFS(AND(ISBLANK(ANY(SELECT(Report Table[Value], AND([_THISROW-1].[ID] = [ID], [_THISROW-1].[Date] = [Date])))),
MAX(SELECT(Report Table[Value], AND([_THISROW-1].[ID] = [ID], [_THISROW-1].[Date] = [Date]))) -MIN(SELECT(Report Table[Value], AND([_THISROW-1].[ID] = [ID],[_THISROW-1].[Date] = [Date])))>5),

SUBSTITUTE(TEXT(MAX(SELECT(Report Table[Value], AND([_THISROW-1].[ID] = [ID],[_THISROW-1].[Date] = [Date])))-MIN(SELECT(Report Table[Value], AND([_THISROW-1].[ID] = [ID],[_THISROW-1].[Date] = [Date])))),".",","))>>

<<IFS(AND(ISBLANK(ANY(SELECT(Report Table[Value], AND([_THISROW-1].[ID] = [ID], [_THISROW-1].[Date] = [Date])))),
MAX(SELECT(Report Table[Value], AND([_THISROW-1].[ID] = [ID],[_THISROW-1].[Date] = [Date])))-MIN(SELECT(Report Table[Value], AND([_THISROW-1].[ID] = [ID],[_THISROW-1].[Date] = [Date])))<=5),

SUBSTITUTE(TEXT(MAX(SELECT(Report Table[Value], AND([_THISROW-1].[ID] = [ID], [_THISROW-1].[Date] = [Date])))-MIN(SELECT(Report Table[Value], AND([_THISROW-1].[ID] = [ID],[_THISROW-1].[Date] = [Date]))))".",",")))>>

[jaichith]

It depends on formatting in excel or PDF. According to that it will work. Try what @Suvrutt_Gurjar sir said. 

simple example : 

If ([order date] = today (), TODAY,Not Today) 

Formatting has been done in the values which should be returned.  In above case. TODAY will be returned in uppercase Bold , false value will be in Lowercase italics.

Expression doesn't have ability to format as far as my knowledge. 

[Suvrutt_Gurjar]

https://www.googlecloudcommunity.com/gc/AppSheet-Q-A/Do-Format-rules-apply-to-email-templates/m-p/23...

Hi @jaichith 

---

@jaichith wrote:

Expression doesn't have ability to format as far as my knowledge

---

We can certainly format output in bot reports with some approach. This I learned from @AleksiAlkio a few years ago. One of his posts is referred above.

[AleksiAlkio]

That was before IF: & EndIf expression was released. Now you can use that as well 🙂

[Suvrutt_Gurjar]

Yes, true Aleksi. The main point we learned is about the formatting tip of entire expression with required  colors or font boldness so that the result in the report or email reflects with that color/ boldness of font.

[jaichith]

New learnings...!!!