Forum Discussion
SK87
4 years agoHelper III
Create a visualization in tabular form
Hi All, Here is one problem which I am facing while creating a tabular graph - I have values in different columns and need to show minimum & Maximum values in one table of respective columns and ...
- 4 years ago
SK87
Here is the updated file as requested https://www.dropbox.com/t/GpKbc42ndoicPxR5Value (Inch) = VAR String = Data[Value] VAR Items = SUBSTITUTE ( String, " ", "|" ) VAR Length = PATHLENGTH ( Items ) VAR T1 = GENERATESERIES ( 1, Length, 1 ) VAR T2 = ADDCOLUMNS ( T1, "@Value", VAR SingleItem = PATHITEM ( Items, [Value] ) VAR ItemLength = IF ( CONTAINSSTRING ( SingleItem, "'" ) || CONTAINSSTRING ( SingleItem, """" ), LEN ( SingleItem ) - 1, LEN ( SingleItem ) ) VAR Number = LEFT ( SingleItem, ItemLength ) RETURN IF ( CONTAINSSTRING ( SingleItem, "'" ), 12 * IFERROR ( VALUE ( Number ), BLANK () ), IFERROR ( VALUE ( Number ), BLANK () ) ) ) RETURN SUMX ( T2, [@Value] )Min = VAR CurrentAttribute = SELECTEDVALUE ( Data[Attribute] ) VAR MinValue = MIN ( Data[Value (Inch)] ) VAR Foot = QUOTIENT ( MinValue, 12 ) VAR Inch = MOD ( MinValue, 12 ) RETURN IF ( CurrentAttribute = "B", IF ( Inch > 0, Foot & "' " & Inch & """", Foot & "'" ), MinValue )Max = VAR CurrentAttribute = SELECTEDVALUE ( Data[Attribute] ) VAR MaxValue = MAX ( Data[Value (Inch)] ) VAR Foot = QUOTIENT ( MaxValue, 12 ) VAR Inch = MOD ( MaxValue, 12 ) RETURN IF ( CurrentAttribute = "B", IF ( Inch > 0, Foot & "' " & Inch & """", Foot & "'" ), MaxValue )Mode = VAR T1 = ADDCOLUMNS ( Data, "@Frequency", CALCULATE ( COUNT (Data[Value] ), ALLEXCEPT ( Data, Data[Value (Inch)] ) ) ) VAR T2 = TOPN ( 1, T1, [@Frequency] ) VAR T3 = DISTINCT ( SELECTCOLUMNS ( T2, "@Value", [Value], "@ValueInch", [Value (Inch)] ) ) RETURN CONCATENATEX ( T3, [@Value], UNICHAR ( 10 ), [@ValueInch], ASC )
tamerj1
4 years agoCommunity Champion
SK87
Ok, Please try the following and see if yu get some blank results in order to narrow down our search for the value that generates the error.
Value (Inch) =
VAR String = Data[Value]
VAR Items =
SUBSTITUTE ( String, " ", "|" )
VAR Length =
PATHLENGTH ( Items )
VAR T1 =
GENERATESERIES ( 1, Length, 1 )
VAR T2 =
ADDCOLUMNS (
T1,
"@Value",
VAR SingleItem =
PATHITEM ( Items, [Value] )
VAR ItemLength =
IF (
CONTAINSSTRING ( SingleItem, "'" ) || CONTAINSSTRING ( SingleItem, """" ),
LEN ( SingleItem ) - 1,
LEN ( SingleItem )
)
VAR Number =
LEFT ( SingleItem, ItemLength )
RETURN
IF (
CONTAINSSTRING ( SingleItem, "'" ),
12 * IFERROR ( VALUE ( Number ), BLANK () ),
IFERROR ( VALUE ( Number ), BLANK () )
)
)
RETURN
SUMX ( T2, [@Value] )SK87
4 years agoHelper III
Thanks this is working fine.
But I don't want to convert the values of (A,B,D,E) columns to Foot, they should be in Inches only. Only column C should be converted to get MIN and MAX values but I need original values in the MIN /MAX for C as well
It should be
| MIN | MAX | Should be | |
| A | 0' | 1446'4" | 17356 |
| B | 0' | 1041'8" | 12500 |
| C | 0' | 3721'10'' | 97 1/4" |