Straight table expression not working?

[#1]

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.

[#2]

Where am I missing single quote? please let me know

[Anonymous]

What about this?

if(Status='Open', NUM(DataColumn1-DataColumn2))

or

if(Status='Open', (DataColumn1-DataColumn2)).

[Mark_Little]

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

[#5]

Thanks. Tried not working.

[Mark_Little]

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

[#7]

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

[sunny_talwar · Accepted solution]

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))

[MarcoWedel]

maybe just a typo in your example:

if(Status='Open', NUM(DataColumn1-DataColumn2))

regards

Marco

[rwunderlich]

Are DataColumn1, DataColumn2, fields in your data? Or labels in your chart?

-Rob

[jagan]

Hi,

Try like this using Sum() instead of Only()

Sum({<Status = {'Open'}>}DataColumn1)-Sum({<Status = {'Open'}>}DataColumn2)

Regards,

Jagan.