CalendarXstQtr 1

[mfroggie]

I am trying a formula to pull sales numbers by qtr using Calendar1stQtr, etc and get "a number is required here" highlighting Calendar1stQtr. My formula is as follows. I don't have a date but am using period.

IF {ICSTATI.PERIOD} in Calendar1stQtr then {ICSTATI.SALESAMT} Else 0

Any advise would be greatly appreciated.

Thanks,

Mfroggie

Mfroggie

 

[lbass]

You need to show us what values are returned by {ICSTATI.PERIOD} and explain what they mean, if not obvious.

-LB

 

[mfroggie]

lbass,

Thank you for your response. The {ICSTATI.PERIOD} field returns 1, 2, 3...... 12 representing the 12 fiscal periods in a fiscal calendar. Hope this helps.



Mfroggie

Mfroggie

 

[lbass]

Do they correspond to months? I.e., does 1 = January, 2 = February, etc.?

-LB

 

[mfroggie]

It actually would depend on the fiscal calendar the company is using, whether they use a calendar yr as their fiscal calendar or a fiscal yr. This particular company uses a fiscal calendar with their 1st month being October so that October is 1, November is 2, etc.

Thanks,

Mfroggie

Mfroggie

 

[lbass]

I think it might be simplest to create a formula {@Qtr} like this:

if {ICSTATI.PERIOD} in 1 to 3 then "Qtr 1" else
if {ICSTATI.PERIOD} in 4 to 6 then "Qtr 2" else
if {ICSTATI.PERIOD} in 7 to 9 then "Qtr 3" else
if {ICSTATI.PERIOD} in 10 to 12 then "Qtr 4"

Then you could just use conditional formulas like this:

if {@Qtr} = "Qtr 1" then {ICSTATI.SALESAMT}

Or you could insert a group on {@Qtr} and insert sums on {ICSTATI.SALESAMT} at the group level.

Or you could insert a crosstab, add {@Qtr} as the row or column field, and sum of {ICSTATI.SALESAMT} as the summary field.

-LB

 

[mfroggie]

Thank you so very much, this did it for me. I used it in a crosstab and then set up a parameter so he could print which ever quarter or all quarters.

Thanks again for your help, greatly appreciated.

Mfroggie

Mfroggie