Complex record selection on multiple parameters

[mrpatak]

Working on a record selection formula for creating a report with multiple charts, sorted by which "goal" they pertain to.
A few of these charts need to display a certain date range stored in the database, the rest need to display a rolling year. The date range is essentially a financial year for these ones, but are not all the same.
I have the rolling year working, but cannot get the records that need the date range specified to work.
Information about the table/data/formulas:
table1 = information about each goal (name, definitions of groups it belongs to, date range for financial year if necessary)
table2 = data for each goal
report_month = date for which data is being reported
data_through_date = date for which data was collected
?Report Month? = user selection of month the report is being written for
@rollingyear = container for the rolling year specification

DateDiff("m",{table2.report_month},{?Report Month?}) < 12

Currently I'm trying out nested if statements (at the bottom of the code), but that doesn't seem to work.
Any help on this would be really appreciated!

//enabling past month report generation
{table2.report_month} <= {?Report Month?} and

//removes any records that are marked as changes
{table2.is_change} = false and

//removes outdated goals
if {table1.Active} = true and 

//chooses report sorting and criteria
if {?Report Name} = "A" then
{table1.a} = true

else
if {?Report Name} = "B" then
{table1.overall_order} <>0

else
if {?Report Name} = "C" then
{table1.c}= true

else
if {?Report Name} = "D" then
{table1.d} = true

else
if {?Report Name} = "E" then
{table1.e} = true

else
if {?Report Name} = "F" then
{table1.f} = true

else
if {?Report Name} = "G" then
{table1.g} = true
else

//select p1 and p2 records from start to end if month is within p1 or p2, otherwise print all within rolling year

if {table1.p2} = true then
   (if {table2.data_through_date} in {table1.p2_start} to {table1.p2_end} then
    {table2.data_through_date} >= {table1.p2_start}
    else {@rollingyear})
else
if {table1.p1} = true then
    (if {table2.data_through_date} in {table1.p1_start} to {table1.p1_end} then
    {table2.data_through_date} >= {table1.p1_start}
    else {@rollingyear})
else
{@rollingyear}

 

[MCuthill]

Mr Patak,

Assuming all else works, I think I see something of note in your IF statements -- namely the structure.

Structure: IF [Criteria] THEN [Result True] ELSE [Result False]
*In your IF's you have a Greater Than Evaluation in the [Results True]

See [red]RED[/red] text for the seemingly missing components.

if {table1.p2} = true then
   (
    if {table2.data_through_date} in {table1.p2_start} to {table1.p2_end} then
    [red]IF [/red]{table2.data_through_date} >= {table1.p2_start} [red]THEN [Something][/red]
    else {@rollingyear}
   )
else
if {table1.p1} = true then
    (
    if {table2.data_through_date} in {table1.p1_start} to {table1.p1_end} then
    [red]IF [/red]{table2.data_through_date} >= {table1.p1_start} [red]THEN [Something][/red]
    else {@rollingyear}
    )
else
{@rollingyear}

Where the [Something] is a value, datafield, or single result.

Alternately, if you intended it to be a second criteria of the same if...

if {table1.p2} = true then
   (
    if {table2.data_through_date} in {table1.p2_start} to {table1.p2_end} [red]AND [/red]{table2.data_through_date} >= {table1.p2_start} [red]THEN [Something][/red]
    else {@rollingyear}
   )
else
if {table1.p1} = true then
    (
    if {table2.data_through_date} in {table1.p1_start} to {table1.p1_end} [red]AND[/red] {table2.data_through_date} >= {table1.p1_start} [red]THEN [Something][/red]
    else {@rollingyear}
    )
else
{@rollingyear}

Hope this helps!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[MCuthill]

Mr Patak,

At another glance... I am now unsure what you are looking to acheive with the current structure and criteria.

If "{table2.data_through_date} in {table1.p2_start} to {table1.p2_end}" is true, then wouldn't "{table2.data_through_date} >= {table1.p2_start}" also always be true (logic below)? IN functions the same as a Greater Than or Equal To paired with a Less Than Or Equal To -- so you can quite probably turf the entire second phrase from both IF's.

Logic
{table2.data_through_date} in {table1.p2_start} to {table1.p2_end}
is the same as:
{table2.data_through_date} >= {table1.p2_start} AND {table2.data_through_date} <= {table1.p2_end}

Please advise if I am out to lunch on both of these - I did read your post in a little more haste than usual.

Cheers!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[mrpatak]

Taking this one as an example:

if {table1.p2} = true then
   (if {table2.data_through_date} in {table1.p2_start} to {table1.p2_end} then
    {table2.data_through_date} >= {table1.p2_start}
    else {@rollingyear})
else

What I'm trying to do is that if the data being brought in, {table2.data_through_date}, is between the two dates specified, then it should only use the data from {table1.p2_start} (a date) on.
So it's neither an additional criteria or another nested if statement, it is the value if true.
To attempt to put that in better terms (as I'm getting lost just re-reading what I wrote),
Criteria: {table2.data_through_date} in {table1.p2_start} to {table1.p2_end}
Value if true: {table2.data_through_date} >= {table1.p2_start}
Value if false: {@rollingyear}

 

[MCuthill]

mrpatak,

As per my first reply above:
Structure: IF [Criteria] THEN [Result True] ELSE [Result False]
*In your IF's you have a "Greater Than Evaluation" in the [Results True] << you cannot return an expression.

Both possible results of an IF statement need to be of the same data type and a single element (both return a Date, both return a number, both return text, etc). If it is an expression, like in your example -- it expects another IF or it is just an invalid result. My apologies if too blunt, but think of it this way. What number is "{table2.data_through_date} >= {table1.p2_start}"? As {@rollingyear} will be a number, the other result needs to be a number as well.

If this is still going in circles, perhaps a specific example with actual Data Values and the expected results would be a good place to start.

Hope this helps! Cheers!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."