Difficulty with Date Comparisions in Set Analysis

[#1]

GOAL

I'd like to use set analysis to filter records based on dates. In this case, I want to find records that have an invoice date before the PO date.

NOTE: All of my code examples below are to find records whose invoice date is equal (not before) PO date because I'm uncertain how to use a less than operator in set analysis in this case. I'm therefore starting with the easiest comparison of equals. I have verified that there are in fact records in my data set where invoice date is the same as PO date.

QV Version

10 SR2

CODE

I've tried these six variations, but none work. I've read that QV sometimes treats dates differently and I was concerned that in my code below one date had a format like this '03/15/09' while another had a format like this '39887'. I therefore attempted using the DATE() function to format the data consistently, but it didn't work either.

Any suggestions would be enormously appreciated!

SUM(
{$< InvoiceDate = {$(=PO_Date)} >}
Amount)
SUM(
{$< InvoiceDate = {'$(=PO_Date)'} >}
Amount)
SUM(
{$< InvoiceDate = {$(=DATE(PO_Date,'DD/MM/YYYY'))} >}
Amount)
SUM(
{$< InvoiceDate = {'$(=DATE(PO_Date,'DD/MM/YYYY'))'} >}
Amount)
SUM(
{$< DATE(InvoiceDate,'DD/MM/YYYY') = {$(=DATE(PO_Date,'DD/MM/YYYY'))} >}
Amount)
SUM(
{$< DATE(InvoiceDate,'DD/MM/YYYY') = {'$(=DATE(PO_Date,'DD/MM/YYYY'))'} >}
Amount)

[danielrozental]

I don't think you're going to be able to achieve that through set analysis as everything in the $() gets resolved before the graph gets calculated, and not once per record as you might think.

Try something like this

SUM(
IF (InvoiceDate<PO_Date, Amount)

)

[Oleg_Troyansky]

there are several important issues to consider here... let's walk through them one by one.

1. Every time you consider using Set Analysis in a chart, keep in mind that Set Analysis expressions get evaluated once for the whole chart, not for each line or cell. Therefore, Set Analysis condition cannot be sensitive to your Chart Dimensions. So, if any of your dimensions need to be associated with your PO_Date, Set Analysis can't be used in this case...

2. Assuming that there is no problem with p.1, the next problem is how to formulate a Set Analysis condition... You need to understand how Set Analysis Modifier is constructed. The format is:

   Field = {value}.

So, keeping this in mind,  your last 2 versions are using incorrect syntax.

3. Next, how can you compare fields using > or < instead of =... This is done through Search functionality, which is specified by a pair of double quotes:

Field = {"search condition"}, so in your case something like this:

SUM(
{$< InvoiceDate = {"<=$(=DATE(PO_Date,'DD/MM/YYYY'))"} >}
Amount)

4. Finally, dates are especially tricky in Set Analysis. For a date comparison to work correctly, the value should be formatted exactly the same way as the Date field itself. So, in your example, make sure that the Date field is formatted with the same DD/MM/YYYY

good luck!

Oleg

[swuehl]

Hi,

I think Daniel is right if you are using above expressions in a chart with date (PO_Date) dimension.

If this is not the case (for example using the expressions in text box),

please try

= sum({$<InvoiceDate={"<$(=max(PO_Date))"}>} Amount)

this should filter InvoiceDate older than PO_Date (hm, maybe using min should be better in that place? I use max to retrieve one and only one value form PO_Date selection. In case you select only one, min and max should be equal).

If you select PO_Date, your text box result should change accordingly.

Please assure that PO_Date and InvoiceDate are both really in date format. If you reformat the dates with e.g. Date(InvoiceDate,'YYYY-MM-DD') in a list box expression, this should result in according format for all records.

If not, you may need to use Date# function while reading in the data.

Regards,

Stefan

[#5]

Hi there, probably you can try something similar to the following, although it is untested.

SUM(
{$< InvoiceID = {"=InvoiceDate<=PO_Date"} >}
Amount)

Regards