Calculating YTD budget

[sairaochowdary]

I have a problem with calculating YTD for a column.The
report contains appl.name,Monthly Budget,Monthly
volume(amount).The monthly budget and monthly volume varies each month.I would
like to know how to calculate the YTD Budget,Total Budget and YTD Volume.Pls send
me the filter condition to be used to solve the
problem.Actually what i need is I have a prompt for
report month ,By selecting a particular month i have
to get the YTD volume till to that month.It is most
urgent.

 

[BruceReed]

What have you got so far? YTD & MTD are set up by default time measure wizards in Transformer.

Bruce

 

[sairaochowdary]

Hi Bruce Reed
I am designing report in Impromptu.In my report i have to calculate above fields using Monthly budget amount and Monthly volume
Thanks
sairaochowdary

 

[sairaochowdary]

Hi Bruce
One more question .I have a picklist prompt with group 1 to group 20.It is showing like
group1
group11
group12
group2
group21
group3
group31
i checked ascending in prompt also eventhough it is showing in the picklist prompt like that only .is there any way to list them in ascending order
Thanks
sairaochowdary

 

[BruceReed]

Hello Sairao

I am still not certain what your 1st question means. I am assuming that you want to calulate (monthly budget - monthly valume MTD). In Impromptu just create a calculated field to do this.

group 01
group 02
group 03
etc.

The sorting is alphabetic for picklists, not numeric.

Bruce

 

[nagrajm]

Hi Sairao

As Bruce indicated, if your picklist items were of the form

group01
group02
group03
....
group21 etc. they would be more amenable to sorting. See if you could rephrase them.

As for your original post, one cannot profer an opinion without more details like the structure of your table. Off the bat I would say that calculation of a YTD number would involve a cumbersome calculation that goes like this

YTD Volume = if (?prompt? = "Jan&quot then (JAN_VOLUME) else if (?prompt? = "Feb&quot then (JAN_VOLUME + FEB_VOLUME) else if (.....................). This could probably be written using DECODE easily.

The above calculation assumes that your table structure is

APPL_NAME
JAN_BUDGET
JAN_VOLUME
FEB_BUDGET
FEB_VOLUME
etc.

In addition to the YTD numbers if you wish to show individual months that make up the YTD number, the report could get complicated. By default, display all 12 columns, however show zeros for months that are irrelevant. For instance, if the user wants to see YTD July, display zeros for all months Aug-Dec.

I hope this helps.

Nagraj

I want to reiterate here that it would be easier for members of the forum to respond quickly and in a more meaning way if the questions posted contain all relevant facts and figures not leaving anything to guesswork. I am assuming that many members don't respond for lack of all inputs that could lead to an intelligent solution. I am sure the members are busy poeple and would like to understand a problem fast and respond in as little time as possible.

 

[sairaochowdary]

Hi nagraj
My question is as follows.I have 2 driving parameters in my report(monthly budget and Monthly volume).Using these two parameters i have to calculate q2 budget,total budget,YTD budget,YTD volume and YTD average.I want the conditions to be used in impromptu to solve this.
Thanks
sairao chowdary

 

[nagrajm]

Hi Sairao

Your particulars are quite vague at this point. I really don't understand what you mean by 'driving parameters' and 'conditions to be used in impropmptu'. If you are referring to filter conditions, your original post refers to 'report month'. Other than that I do not think you need any other detail filter condition, if you follow exacly what I have mentioned in my last post. If you think I am missing something, let me know with more details.

 

[sairaochowdary]

Hi Nagraj
In my catalog i have only Monthly budget and monthly volume columns.The basic thing i need is ,I donot have columns like jan_volume,feb_volume etc..what i want is i have to add prev month monthly budget to this month monthly budget while calculating the Q2 budget .i am not getting what logic to use for adding prev month to the present month.i have a prompt also in my report
pls give me the good suggestion .
Thanks
sairao chowdary

 

[nagrajm]

Hi Sairao

Your details are still not clear when you say you want to add "previous month monthly balance to this monthly balance". You are not clear as to how this data is organized in your tables. I guess you can begin by giving your table structure and mapping it to your report format. Without this information, no forum member can give an intelligent suggestion.

Nagraj

 

[BruceReed]

Hello Sairao

It sounds like you want to calculate a running total on budget.

EX Running Total(Monthly Budget-Monthly Actual) for each month.

Is this what you are trying to do? You will probably have to use group totals (for every month), then use this group totals to come up with the prior month's number. Impromptu cannot write back to the database & I foresee some difficulties.

Bruce

 

[sairaochowdary]

Hi Nagraj
Thanks for responding.I have to calculate the Q1,Q2,Q3,Q4 Budgets.I have prompt in my report(Report Month).I enter into report using this prompt .My doubt is how to add Prev.month "monthly Budget" to this month(suppose Iam in Feb report I want to add the Jan monthly budget to this month).Iam not getting how to add prev month budget to this month in current month report.
Thanks
Sairao chowdary