If-Then-Else problem in Chart 1

[southbean]

Hello All,

I can’t figure this out. I’m using the following formula (@OpenClose) in a chart.

Used as is, I get returned the correct data for the first ‘IF’ statement, but no data for the second ‘IF’ statement.

If I swap the order of the ‘IF’ statements, I get the correct data for the first (previously second) ‘IF statement but not the second.

What am I doing wrong?!?!?!?

Thanks for any/all help or advice.

- Tom

// @OpenClose
If {Table.String1} = "Text1" AND {Table.Date1} IN YearToDate  
Then "Output1" ELSE
IF {Table.String1} = "Text1" AND {Table.String2} = "Text2" 
AND {Table.Date2} IN YearToDate 
Then "Output2"

 

[aaronburro]

crazy idea, but have you tried the following:

IF ({Table.String1} = "Text1") THEN
(
IF ({Table.Date1} IN YearToDate) THEN
"Output1"
ELSE IF (({Table.Date2} IN YearToDate) AND ({Table.String2} = "Text2")) THEN
"Output2"
ELSE
""
)
ELSE

 

[southbean]

Hi aaronburro,

Thank you for your reply!

Unfortunately, I get the same result. Even when I swap the order in your script.

Makes no sense to me.

Anyway, thanks again for your quick post.

- Tom

 

[lbass]

Could it be that your two formula results are not mutually exclusive, i.e., could both conditions apply to the same record? I think you should try laying out the data in the detail section to see whether the data really separates into two "groups" or whether you are actually hoping for the same record to appear twice.

I'm assuming you are using this formula in the "on change of" area, with some value as your summary.

-LB

 

[southbean]

Hi LB,

Thank you for your reply!

You've got it exactly right, of course. The data is NOT mutually exclusive. And yes, I am using the formula in the "on change of" area in the chart.

What I want to do is count records that have been:

1. created (table.create_date) and
2. closed (table.closed_date) in the past YTD

All records will have a 'create_date' and only some records will have a 'closed_date'.

I would greatly appreciate any suggestions on how to get this count in a chart.

Thanks again!

- Tom

 

[lbass]

You could use running totals, one counting created dates and the other closed dates. See thread767-1089708 for help with that. Or, you could try a union all where you merge the created and closed dates into one field, and use another field to distinguish the two.

-LB

 

[southbean]

Hi LB,

Thanks for your reply.

I tried the running total solution, but the chart wouls not accept them (I'm using CR 10 Stnd). I then tried to use formulas to replicate the RT

 If {Table.String1} = "Text1" AND {Table.Date1} IN YearToDate Then 1 ELSE 0

But that got me the same original result. I'm interested in trying the Union query. Could you explain a little further how that would work?

Thanks again!

- Tom

 

[lbass]

The chart should accept the running totals as shared variables, if you are careful to only create, but not add, the shared variable formulas to the subreport before adding the formulas to the running total.

To do the union all, use "Add Command" as your datasource. Then enter something like the following (syntax will vary):

Select
Table.`Date1`,'Date1' as DateType, Table.`String1`,Table.`String2`
From
`Table` Table
Union All
Select
Table.`Date2`,'Date2' as DateType, Table.`String1`,Table.`String2`
From
`Table` Table

You can then add a record selection formula of:

{Command.Date1} in YearToDate

Then you would use {Command.DateType} in your formula for the on change of field in the chart.

-LB

 

[southbean]

Hi LB,

Thanks again for posting your reply!

I'm not working with any subreports. And yet I still can't get the chart expert to accept the Shared Variable RT or Formula if it used a RT field.

I would have liked to have used the Union query to solve this problem I'm having, but the two tables do not have matching fields.

I never would have imagined this would be so diffucult!?!?!?
All I need to do is count records in a chart!

Anyway, thanks again for all you're help!

- Tom

 

[lbass]

First of all, the running total solution REQUIRES using a subreport. Please reread the thread I provided the link to.

Secondly, WHAT two tables??? You can use fake fields as matches in a union all. Your posts so far have should only one table. Please provide the actual table and field names, and you might still be able to do the union all.

-LB

 

[southbean]

Hi LB

Thanks for being so patient.

I have two tables I’m working with. These are the columns I’m using from each for this chart.

Table1:
ID
Open_Date
Completed_Date
Form_Category


Table2:
ID
Function
Target

Thanks again for your help!

- Tom

 

[kskid]

Try this

1. created (table.create_date) and
2. closed (table.closed_date) in the past YTD

In these particular cases, I use the SQL Expression

SQL Expression
%Created
(
Select (t.create_date)
from table t
where t.create_date in yeartodate
and t.id = table.id
)

In the record selection formula, add the following

{table.closed_date} = %created

-lw

 

[kskid]

Typo should be


{table.create_date} = %created

 

[southbean]

Hello kskid,

Thank you for your reply,

Unfortunately, I'm having to use a SQL Command as a datasource for this report and - as far as I can tell - am not able to use a SQL Expression.

Do you know of a work around?

Thanks again!

- Tom

 

[lbass]

I am unclear how the SQL expression would address the issue of the two fields being in the same record. To do the union all with two tables, use syntax something like:

Select
Table1.`ID`,Table1.`Open_Date`,'Open Date' as DateType, Table1.`Form_Category`, Table2.`ID`, Table2.`Function`, Table2.`Target`
From
`Table1` Table1, `Table2` Table2
Where
Table1.`ID` = Table2.`ID`
Union All
Select
Table1.`ID`,Table1.`Completed_Date`,'Completed Date' as DateType, Table1.`Form_Category`, Table2.`ID`, Table2.`Function`, Table2.`Target`
From
`Table1` Table1, `Table2` Table2
Where
Table1.`ID` = Table2.`ID`

-LB

 

[southbean]

LB,

Thank you for hanging in there with me. That worked perfectly. You REALLY are the best! This technique you suggested (using a Union query) was new to me so I didn’t quite understand where you were going with it at first. However, it was exactly what was required and I’m sure I’ll be using it again in the future.

Thanks again very much,

- Tom