6 Replies Latest reply: Dec 7, 2016 12:41 PM by Jean phill

# How to calculate a turnover per year

Hello,

I have a table called "Sales" with the date of the operation and the amount of the operation.

I'm trying to create a table with customers' details and for each customer it's turn over per year (for the last 3 years).

How could I resolve the problem please?

• ###### Re: How to calculate a turnover per year

Excuse me

What I need is a YTD and not simple sales per Year

Tx

JP

• ###### Re: How to calculate a turnover per year

Lets say you have a loaded table called 'Sales' with fields CustomerId, TransactionDate and TransactionAmount...

You could create another table that would sum the sales for each customer for the previous year like so...

CustomerData:

Distinct CustomerId

, Sum(if(inyear(TransactionDate, Today(),-1),TransactionAmount)) AS sumOfSalesForLastYear

Resident SalesData

Group By CustomerId

;

Have a look at the inyear function in the QS help documentation.  You will need to modify the above code for the specifics of your turnover calculation.

Hope that helps.

• ###### Re: How to calculate a turnover per year

Dear Brian,

I'll try to implement your recommandation

Tx

• ###### Re: How to calculate a turnover per year

Dear brianrmacdonald

How to specify that the fiels is numeric please (the sum) because in the reporting, it is shown as a string field?

JP

• ###### Re: How to calculate a turnover per year

Does the sum() function compute correctly?  If so, I am not sure how the result could be a string, but you could use the Num#() function?

Num#() converts a text string to a numerical value, in the number format set in the data load script or the operating system. Custom decimal and thousand separator symbols are optional parameters.

• ###### Re: How to calculate a turnover per year

Tried to use Num#() but when I add the field to a table (as a dimension), it will add it as a text.

So to resolve my issue, I added the field as a measure

Tx