crystal report syntax to pull year to date info. 2

[ccclsi]

Hi, i know this is noob issue but i need to get transaction info for previous 12 months and previous 6 months with monthly average for each.

Here is select expert:
{INVENTORY_TRANS.PART_ID} like {?PART_ID} and
{INVENTORY_TRANS.TRANSACTION_DATE} >= Date (0, 0, 0)

makes sense to me: {INVENTORY_TRANS.TRANSACTION_DATE} >= (currentdate-12months) and {INVENTORY_TRANS.TRANSACTION_DATE} >= (currentdate-6months)

I just don't understand how to write this.

Using Crystal 8.5 and oracle 8i. TIA

 

[IanWaterman]

In select expert use

{INVENTORY_TRANS.TRANSACTION_DATE} >= dateadd("yyyy", -1, currentdate)

To differentiate last 12 and 6 months you will need to right formual like

All value fields returned will now be 12 months for 6 months use

@6mthvalue
If {INVENTORY_TRANS.TRANSACTION_DATE} >= dateadd("m", -6, currentdate) then {valueField} else 0

YOu can then summarise this formula

Ian

 

[ccclsi]

actually, i don't know what i am talking about cause to get this info on 1 report i would need to pull the data via formulas right? 1 for last 12 months and 1 for last 6 mo. Not use select expert. Sorry for my confusion.

 

[CR85user]

The information for 0 -> 6 months is the same as that shown within the selection for 0 -> 12 months.

What Ian is explaining is that your selection criteria just needs to retrieve the last 12 months and not mention 0 -> 6 months.

Once you have the last 12 months data you can then create a formula to ditinguish between the two for calculations such as averages etc.

Exactly as Ian suggested.

Try and use the example code Ian provided and then post back if it isn't working in the way you need.

'J

CR8.5 / CRXI - Discovering the impossible

 

[ccclsi]

Thank you guys alot for the help. I got an acccurate count for 12 months, but i don't know how to write the formula for only 6 months. When i put in: If {INVENTORY_TRANS.TRANSACTION_DATE} >= dateadd("m", -6, currentdate) then {valueField} else 0

i get an alert that {valueField} is not a known field. i understand the concept, i think. it seems valuefield should be the field i input for the 6month sum, Right? But then i try that and it says i can't use itself as field name, so i try sumTrans_line.qty and that does not work. So i am getting there but still stuck. Thanks for your help!

 

[CR85user]

Replace {valuefield} with the field you are trying to average.

If you have a field called {database.qty} then insert that in place of {valuefield}.

You can then perform totalling / averages etc based on this fields results.

Basically,

If you are averaging {database.field1} for your 12 month total, this formula will allow you to get the figures for the last 6 months and not count 6 -> 12 months.

'J

CR8.5 / CRXI - Discovering the impossible