Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I am trying to create an auto number for my rows under a table using RowNo(), but I am not getting what I want. The original table is only showing the values that match my expressions and what I want is for the RowNo() to generate only for those values that are shown in the table and that match the expression. I want to use the RowNo() as a sort of alert unique number for that data value.
When I place the RowNo() it brings all the values for my data, not only the ones that are true due to the calculations, and it also repeats the number is some instances. Does anybody know how I can generate a true RowNo() for only the rows that are showing in the table due to the expressions I applied? I can provide a detail of the data if needed. Thank you.
Hi,
Can you provide sample data?
May be try this expression...
=if(not isnull([YourExpression Label Name],RowNo(TOTAL))
Would be good to know how many dimensions you have and what expressions do you have in your chart?
Hi ,
Post your sample app-Data or how many dimensions your using ??
-Hirish
Thank you to everyone that replied. So I have 2 tables. In two different applications that I want the row number to generate in the same way.
The simplest table has 3 dimensions and 3 expressions (adding one with the Row Number)
The more complicated table has 3 dimensions and 6 expressions (adding one with the Row Number)
What I want is to calculate the column shown in Red in the table below. When I use either the RowNo() or the RowNo(TOTAL) function it brings back all the relationship numbers that do not meet my expressions and gives them a row number. I do not want this. And even when I hide the null value in some rows or use the
=if(not(isnull([Relationship Number])),RowNo(TOTAL)) expression, it generates the number up to 65, where I only want it to be max 19 in this case since only 19 relationship numbers meet my expressions.
The expressions that I have are the following:
The fields you see me using in my expressions where calculated within my script.
For the Relationship Number - I have the RELATIONSHIP_NUMBER field also as a dimension but it is hidden in the straight table. I added it here as an expression as well in order to get the unique count of alerts by relationship number.
(TotalTran > '0' and RISK_RATING = '01' and TotalAmount > BAL_Threshold_LOW, RELATIONSHIP_NUMBER,
IF(TotalTran > '0' and RISK_RATING = '02' and TotalAmount > BAL_Threshold_MED, RELATIONSHIP_NUMBER,
IF(TotalTran > '0' and RISK_RATING = '03' and TotalAmount > BAL_Threshold_HIGH, RELATIONSHIP_NUMBER,
IF(TotalAmount > '1000' and RISK_RATING = '01' and TotalTran >= TRAN_Threshold_LOW, RELATIONSHIP_NUMBER,
IF(TotalAmount > '1000' and RISK_RATING = '02' and TotalTran >= TRAN_Threshold_MED, RELATIONSHIP_NUMBER,
IF(TotalAmount > '1000' and RISK_RATING = '03' and TotalTran >= TRAN_Threshold_HIGH, RELATIONSHIP_NUMBER))))))
For Balance Exception
(TotalTran > '0' and RISK_RATING = '01' and TotalAmount > BAL_Threshold_LOW, 'Exception - Bal',
IF(TotalTran > '0' and RISK_RATING = '02' and TotalAmount > BAL_Threshold_MED, 'Exception - Bal',
IF(TotalTran > '0' and RISK_RATING = '03' and TotalAmount > BAL_Threshold_HIGH, 'Exception - Bal')))
For Tran Exception
(TotalAmount > '1000' and RISK_RATING = '01' and TotalTran >= TRAN_Threshold_LOW, 'Exception - Tran',
IF(TotalAmount > '1000' and RISK_RATING = '02' and TotalTran >= TRAN_Threshold_MED, 'Exception - Tran',
IF(TotalAmount > '1000' and RISK_RATING = '03' and TotalTran >= TRAN_Threshold_HIGH, 'Exception - Tran')))
Bank | Risk | Relationship Number | Balance Exception | Tran Exception | Unique ID | RowNo(TOTAL) |
Alert Totals | 19 | 14 | 10 | |||
1 | LOW | 123 | Exception - Bal | - | 1 | 2 |
1 | LOW | 234 | Exception - Bal | - | 2 | 7 |
1 | LOW | 345 | Exception - Bal | - | 3 | 12 |
1 | LOW | 489 | Exception - Bal | - | 4 | 13 |
1 | LOW | 552 | Exception - Bal | - | 5 | 14 |
1 | MEDIUM | 678 | Exception - Bal | Exception - Tran | 6 | 19 |
1 | MEDIUM | 789 | - | Exception - Tran | 7 | 24 |
1 | MEDIUM | 899 | Exception - Bal | - | 8 | 25 |
1 | MEDIUM | 911 | Exception - Bal | - | 9 | 26 |
1 | MEDIUM | 1025 | Exception - Bal | - | 10 | 30 |
1 | HIGH | 1136 | - | Exception - Tran | 11 | 31 |
2 | MEDIUM | 1285 | Exception - Bal | Exception - Tran | 12 | 44 |
2 | MEDIUM | 1378 | Exception - Bal | Exception - Tran | 13 | 47 |
2 | MEDIUM | 1452 | Exception - Bal | - | 14 | 54 |
2 | MEDIUM | 1589 | Exception - Bal | Exception - Tran | 15 | 61 |
2 | HIGH | 1696 | - | Exception - Tran | 16 | 62 |
2 | HIGH | 1778 | Exception - Bal | Exception - Tran | 17 | 63 |
2 | HIGH | 1823 | - | Exception - Tran | 18 | 64 |
2 | HIGH | 1911 | - | Exception - Tran | 19 | 65 |
hi maria,
Maybe in expression tab Select option expression total instead of max rows under total mode as per your requirement
Not sure, how to exclude the rows using the if condition. Normally, it would be,
=Aggr(RowNo(TOTAL),Dim1,Dim2,Dim3) - in this need to use the column is not null.
I tried, but not able to achieve the result.