How to show value on the same row?

[mrichman]

Hi Guys,

Would be nice if you could help me out. How can I show value on the same row?

I have two tables, Table 1 and Table 2 on the backend. On the front end I have a filter on "Level" (see underlying picture)

On the front end I selected Level = A, and Type = Bad ----- This should show a ranking lower than A. So in this case, 2 which is equal to B.

My current if statement is working for 'Good' but for 'Bad' I cant find the right solution. I'm getting ranking 2 and would like to show B.

My current statement is the underlying, I tried playing with variables to get the B but it is not working.

IF(Type='Good',[Level],IF(Type='Bad',Rank+1)) as Test

Do you guys have any idea?

Thanks in advance.

[Anonymous]

Just change table 1 to reflect something like this:

Type      Level

Good      A

Bad         B

Bad         C

Bad         D

Bad         E

Bad         F

[mrichman]

Hi Ruan,

Thanks for your prompt reply. This is not possible. Because if the next selection would be Level=C and Type is Bad, then it should show D.

It needs to be dynamic.

Thanks in advance.

[undergrinder]

Hi Migual,

so the logic is only the selected level is good, and any other value is bad?

I don't really get what is the requirement.

G.

[mrichman]

Hi Gabor,

Thanks for you message the logic should be the following.

If ..... Level is A and Type is Good then show the same as Level, in this case A.

the other part is.

If..... Level is A and Type is Bad then show one Level lower than current selected, in this case B.

So.

Level                Type                Answer

Good                    A                    A

Good                    B                    B

Good                     C                    C

.....

Bad                         A                   B

Bad                        B                   C

Bad                        C                   D

....

Hope this helps

Thanks in advance.

[OmarBenSalem]

Can u share mock table with maybe 10 rows so that I can perform test on it ?

[mrichman]

Added one

[Anonymous]

Well, Because there are so little number of rows

Id suggest creating another column in the Table2 called BadType

Resulting table

Rank     Type  BadType

1           A          B

2           B          C         

3           C          D

4           D          E

5           E          F

6           F          G

And change your formula to IF(Type = 'Good',[Type],[BadType])

[OmarBenSalem · Accepted solution]

Try this :

If(Type='Good',Level,

If(Type='Bad',only({1<Rank={"$(=max(Rank)+1)"}>}Level)))

Result:

to show it on the same table; please alter it as follow:

If(Type='Good',Level,

If(Type='Bad',only(total {1<Rank={"$(=max(Rank)+1)"}>}Level)))

Result:

[mrichman]

Awesome! it worked!!! Thanks!