If you are unable to create a new account, please email support@bspsoftware.com

 

News:

MetaManager - Administrative Tools for IBM Cognos
Pricing starting at $2,100
Download Now    Learn More

Main Menu

Trying to calculate a return rate base on total returns/sales

Started by bignadad, 19 Aug 2013 10:48:38 AM

Previous topic - Next topic

bignadad

This is my first attempt at this

Here is the report
https://dl.dropboxusercontent.com/u/5012364/cognos /report.png

the queries
https://dl.dropboxusercontent.com/u/5012364/cognos /queries.png

the return total im trying to get to my 3rd list
https://dl.dropboxusercontent.com/u/5012364/cognos /returntotal.png

my sales total im trying to get to my 3rd list
https://dl.dropboxusercontent.com/u/5012364/cognos /salestotal.png

the join query im trying to get my return rate for each salesman
https://dl.dropboxusercontent.com/u/5012364/cognos /returnrate.png

Im trying to take the line total for each salesman on sales and returns and display it in a new list on the bottom of those 2


**More Details**
On the returns here is how the total is calculating (From the Joined Query)
[Line total] = [Invoice].[Net Price]*[Invoice].[Quantity Shipped]
That is calculated for each [Invoice].[SalesID]
So that amount I'm trying to get from it is total([LineTotal])
SalesID is created by an expression that is a if then else based on organization and salesman initials
Next the Sales Total is calculated like so (from Sales Query)
total([Invoice Amount])
That is calculated for each Expression above (to get salesman)

Those two list work fine. They calculate the totals for each salesman as they should

The "joined" return query i have the following
[Invoice].[SalesID] JOINED to [SalesID] (salesID being the expression above)
The Return rate data item is [Joined].[TotalAll]/([Joined].[TotalAll]+[Sales].[I nvoiceAmountTotal])
That's what calculates my return rate

For the 3rd list (return rate) i have the following
[Sales].[SalesID] as one column
[Joined].[TotalAll]/([Joined].[TotalAll]+[Sales].[I nvoiceAmountTotal]) as the other column

When I run it I get this error
The 'ces_currentmeasure' function is not supported in the context in which it is used.

I have a prompt that lets user select
Organization
Period
Year

---------------------------------------------

So i was guessing i could not take the running total from a cross tab and place in another list to use for calculations.

My next attempt was to replace the crosstab with a simple 2 column list.
https://dl.dropboxusercontent.com/u/5012364/cognos/simple.png

I then made a new return query and took all the fields from the original sales/joined queries.
So basically this new return query was generating everything again on its own query.

Well it seems i cannot get past setting the Transaction Type. Both sales and returns are based on the invoice amount BUT each depend on the transaction type being CM or DI.


Please give me some insight on this.

I just want to display my returns by salesman, sales by salesman, then calculate the return rate by salesman.