Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
HI,
I have to write a expression based on below condition
IF Status='Open' then NUM(DataColumn1-DataColumn2)
I am trying to use if(Status=Open', NUM(DataColumn1-DataColumn2)). But straight table column is showing blank. table is not loading any data. Is there any error in expression? please can anyone suggest me
Thanks.
Where am I missing single quote? please let me know
What about this?
if(Status='Open', NUM(DataColumn1-DataColumn2))
or
if(Status='Open', (DataColumn1-DataColumn2)).
Hi,
I would guess you are getting a number of results returned for each of the field in your expression, Try something like
if(Status='Open', NUM(MAX(DataColumn1)-MAX(DataColumn2)))
to test.
I think Need to make sure that DataColumn1 and DataColumn2 are just returning one distinct value.
Mark
Thanks. Tried not working.
Hi Amelia,
Can you supply some demo data?
Logically everything suggested would work in a standard example.
There could be a number of reasons you seeing blank values return, like String values, multiple values being evaluated when one is expected.
Mark
here is sample data
Week(EnterDate) | Data1 |
1 | 52 |
2 | 63 |
3 | 107 |
4 | 54 |
5 | 79 |
6 | 80 |
7 | 108 |
8 | 80 |
9 | 78 |
10 | 106 |
11 | 80 |
Data1 is calculated by using expression NUM(DataColumn1-DataColumn2) and Dimensions are Dim1: Week(EnterDate) and Dim2: =IF(MATCH(Status,'Open'),Status), Dim3: CustomerID
Week(EnterDate) | Data2 |
1 | 18.2 |
2 | 17.0 |
3 | 48.0 |
4 | 30.5 |
5 | 20.9 |
6 | 29.3 |
7 | 42.4 |
8 | 30.7 |
9 | 14.5 |
10 | 17.2 |
11 | 34.9 |
Data2 is calculated by using expression NUM(DataColumn3-DataColumn4) and Dimensions are Dim1: Week(EnterDate) and Dim2: =IF(MATCH(Status,'Closed'),Status), Dim3: CustomerID
Week(EnterDate) | Data3 |
1 | 102 |
2 | 99 |
3 | 102 |
4 | 100 |
5 | 106 |
6 | 91 |
7 | 82 |
8 | 84 |
9 | 75 |
10 | 93 |
11 | 98 |
Data3 is calculated by using expression NUM(DataColumn5-DataColumn6) and Dimensions are Dim1: Week(EnterDate) and Dim2: =IF(MATCH(Status,'Required'),Status), Dim3: CustomerID
above all three tables are working fine. but when I am merging data into one straight table blank values are coming. I need a table as below with Dimension: Week(EnterDate) and expressions should be with Data1, Data2, Data3
Week(EnterDate) | Data1 | Data2 | Data3 |
1 | 52 | 18.2 | 102 |
2 | 63 | 17.0 | 99 |
3 | 107 | 48.0 | 102 |
4 | 54 | 30.5 | 100 |
5 | 79 | 20.9 | 106 |
6 | 80 | 29.3 | 91 |
7 | 108 | 42.4 | 82 |
8 | 80 | 30.7 | 84 |
9 | 78 | 14.5 | 75 |
10 | 106 | 17.2 | 93 |
11 | 80 | 34.9 | 98 |
Please help me to sort out this. sorry if I am not clear in my post when I asked at the beginning
Try this may be:
Dimension:
1) Week(EnterDate)
2) CustomerID
Expressions:
Data1: Num(Only({<Status = {'Open'}>}DataColumn1)-Only({<Status = {'Open'}>}DataColumn2))
Data2: Num(Only({<Status = {'Closed'}>}DataColumn1)-Only({<Status = {'Closed'}>}DataColumn2))
Data3: Num(Only({<Status = {'Required'}>}DataColumn1)-Only({<Status = {'Required'}>}DataColumn2))
maybe just a typo in your example:
if(Status='Open', NUM(DataColumn1-DataColumn2))
regards
Marco
Are DataColumn1, DataColumn2, fields in your data? Or labels in your chart?
-Rob
Hi,
Try like this using Sum() instead of Only()
Sum({<Status = {'Open'}>}DataColumn1)-Sum({<Status = {'Open'}>}DataColumn2)
Regards,
Jagan.