14 Replies Latest reply on Jul 30, 2009 9:47 AM by suite48

# Help for omitting records with dates that have multiple price points

### Title

Help for omitting records with dates that have multiple price points

### Post

I'm a beginner, so any help appreciated!  I'm trying to pull data together to calculate price elasticity.

I have a date field and a price (i.e. number) field.  I want to perform a bunch of searches for distinct price points (say starting with one for \$49) and only find records if on that date we sold the product only for that particular price (it is not sold at different prices that date).

E.g. if on February 2 we sold the same product one time for \$49 and one time for \$59, I want to omit any records with that date when searching for \$49.

Any suggestions?

tx,

hans

• ###### 1. Re: Help for omitting records with dates that have multiple price points

Wow, I really don't follow your explanations.  It seems you're saying, if you have the data below:

Product A   1/1/09   \$49

Product A   1/1/09   \$49

Product A   1/1/09   \$59

Product A   1/1/09   \$69

You only want to see:

Product A   1/1/09   \$49

Product A   1/1/09   \$59

Product A   1/1/09   \$69

• ###### 2. Re: Help for omitting records with dates that have multiple price points

Thanks for nailing it down.  I want to exclude the dates on which we had multiple price points, like this:

Product A   1/1/09   \$49

Product A   1/1/09   \$59

Product A   1/2/09   \$49

Product A   1/2/09   \$49

Product A   1/3/09   \$69

I only want to see:

Product A   1/2/09   \$49

Product A   1/2/09   \$49

Product A   1/3/09   \$69

• ###### 3. Re: Help for omitting records with dates that have multiple price points

Ah, that makes sense.  One way to accomplish this is by setting a self-relationship for the table, by product and date.  Then, create a calculation field, with this formula:

Case ( Max ( Table Self:: Price ) = Min ( Table Self:: Price ), Price )

This will give you a field containing the price for each record, but only if the minumum price for that product & day equals the max price.  Search on that field, omitting any empty values, and you should have the list you're looking for.

• ###### 4. Re: Help for omitting records with dates that have multiple price points

Thanks.  I tried that, but somehow the calculation gives me all records with the regular price, not jsut when on that date the min price = max price.

I haven't created a self-relationship for the table yet, but just read up about it.  I believe the way it works is that you link the table (e.g. Table) to a second instance of the table (Table2), but then do you want me to do product=product and price=price for the 2 instances of the table?

Below is a screenshot of the relationship and calculation formula.  Am i missing anything?

thanks...

Below

• ###### 5. Re: Help for omitting records with dates that have multiple price points
Change the 'Evaluate this calculation from the context of' selection to "fi thorw".
• ###### 6. Re: Help for omitting records with dates that have multiple price points
thanks.  I tried that but it didn't make a difference.
• ###### 7. Re: Help for omitting records with dates that have multiple price points
Did you change anything else? The calculation should be evaluated from the context of "fi thorw", and the formula should refer to fields from  "fi thorw 2".
• ###### 8. Re: Help for omitting records with dates that have multiple price points

the funny thing is when i do that (fi thorw is selected as "from the context of") and fi thorw 2 is selected as the current table (screenshot in previous posting), hit ok, i don't see anything changed.  I.e. there are still dates listed that have different price points.

Now when i open the calculation field again, it automatically is reverted back to "current table is fi thorw").  No matter how often i change the table on the left tofi thorw 2, it always shows as fi thorw when opening the calculation again after hitting OK....

• ###### 9. Re: Help for omitting records with dates that have multiple price points

The "Current Table" is meaningless in this context: it shows which TO the fields listed below are coming from. This doesn't affect the existing formula at all. The only thing that matters is the formula and the evaluation context.

suite48 wrote:

there are still dates listed that have different price points.

Do you mean AFTER doing a find for a specific price in the calculation field? Also, is your layout set to show records from "fi thorw"?

• ###### 10. Re: Help for omitting records with dates that have multiple price points

this is after a search for the date 9/1/2007.  Calculation field is on the right.  It lists both the product price filed from fi thorw and fi thorw 2 on the left of it

this is after searching the cacluation field for 40.33

9/1/2007 products shouldn't show up.

It's starting to drive me nuts! :-)

• ###### 11. Re: Help for omitting records with dates that have multiple price points

Maybe this help for trouble shooting (this is the first time I'm working with self-relationships, and that's still a concept that needs to grow on me).

Here is the standard data:

Here is when i change the 50.41 price in 1 record to 8

Does this ring more a bell of what might be wrong?  Again, thanks for all your help!

• ###### 12. Re: Help for omitting records with dates that have multiple price points
Could you show the same thing (as the first one), but also include the product field?
• ###### 13. Re: Help for omitting records with dates that have multiple price points
You have your relationship set to 'product price', not product name, if it exists.  If it doesn't, just set the relationship by date.
• ###### 14. Re: Help for omitting records with dates that have multiple price points

I tried something else that seemed to have done the trick!  i calculated the unique prices for that date through a count: Count ( fi thorw 2::product price ) =1

[the date and product price were still connected through a self-relationship]

thanks again for both of you guys' time and help!