How to calculate a turnover per year

[Anonymous]

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?

Tx by advance

[Anonymous]

Excuse me

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

Tx

JP

[Anonymous]

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:

Load

  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.

[Anonymous]

Dear Brian,

Thank you a lot for your answer.

I'll try to implement your recommandation

Tx

[Anonymous]

Dear brianrmacdonald‌

I just implemented your recommandation.

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

Tx by advance,

JP

[Anonymous]

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.

[Anonymous]

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