cancel
Showing results for 
Search instead for 
Did you mean: 

How to filter according to the annual sales

Former Member
0 Kudos

Hello everyone!

I am new to this community. Just wanted to take some online classes first to know at least what I am talking about. They were pretty informative.

I am working on some reports and stuck at filtering. I am comparing annual sales and what I want to do is to show the accounts which had increase from 2012 to 2013. Is this possible to do?

Accepted Solutions (1)

Accepted Solutions (1)

JWiseman
Active Contributor
0 Kudos

hi Korhan,

1) the best way to do this without using a lot of sql is to create a formula for each year that will represent your sales...e.g. for current year

if year({Orders.Order Date}) = year(currentdate)

then {Orders.Order Amount}

2) and another formula for last year

if year({Orders.Order Date}) = year(currentdate)-1

then {Orders.Order Amount}

3) instead of grouping on Year, group on Customer. now create a summary on each of the formulas above.

4) then in your Report menu > Selection Filters > Grouping create a Group Selection Filter...using the above example

Sum ({@OrdersCurrentYear}, {Customer.Customer Name}) > Sum ({@OrdersCurrentYearMinus1}, {Customer.Customer Name})

please see the attachment as this will help the steps above  make more sense...extract the contents and change the .txt extension to .rpt.

cheers,

-jamie

Former Member
0 Kudos

Jamie,

Thanks for your response. Let me look into this.

I am a newbie so it may take sometime for me to digest.

Thanks.

JWiseman
Active Contributor
0 Kudos

no problem. one thing i forgot to mention is that Group Selection does not filter out any records...it only hides them.

this means that any summaries or charts that you build you may see values larger than the values on the report display...this is because the customers that are hidden are still being totalled up.

in those cases (e.g. in a chart), you would need to use a formula like

if Sum ({@OrdersCurrentYear}, {Customer.Customer Name}) > Sum ({@OrdersCurrentYearMinus1}, {Customer.Customer Name})

then {Customer.Customer Name}

so that you are only dealing with the customers with greater sales in the one year. please see the altered attachment here. it has a chart that uses the above formula so that only the correct customers are displayed.

if things start to get a bit messy when you use a Group Selection formula, then it's advisable to write your own sql in a Command object that actually does a filter (i.e. sql WHERE clause) instead of what the group selection does, which is just hiding the values.

cheers,

jamie

Former Member
0 Kudos

Jamie,

Everything was fine until I tried to enter a chart. Then, as you mentioned, it shows all the data. Where exactly do I enter that formula to filter further.

Where would you put that formula in your report?

Thanks.

JWiseman
Active Contributor
0 Kudos

have a look at the revised report attached in the post above, Feb 11, 2014 6:51 PM.

there's a Customers formula that you should have a look at. it is used in the chart's On Change Of value.

-jamie

Former Member
0 Kudos

Don't see any revised report.

Former Member
0 Kudos

Sorry, found it.

Answers (0)