topic Re: How to calculate a turnover per year in App Development

How to calculate a turnover per year

Anonymous — Mon, 05 Dec 2016 13:48:40 GMT

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

Re: How to calculate a turnover per year

Anonymous — Mon, 05 Dec 2016 15:22:05 GMT

Excuse me

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

Re: How to calculate a turnover per year

Anonymous — Tue, 06 Dec 2016 00:01:54 GMT

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.

Re: How to calculate a turnover per year

Anonymous — Wed, 07 Dec 2016 09:51:36 GMT

Dear Brian,

Thank you a lot for your answer.

I'll try to implement your recommandation

Re: How to calculate a turnover per year

Anonymous — Wed, 07 Dec 2016 10:58:11 GMT

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,

Re: How to calculate a turnover per year

Anonymous — Wed, 07 Dec 2016 16:07:06 GMT

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

Anonymous — Wed, 07 Dec 2016 17:41:03 GMT

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