Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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?

Tx by advance

6 Replies
Anonymous
Not applicable
Author

Excuse me

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

Tx

JP

Anonymous
Not applicable
Author

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
Not applicable
Author

Dear Brian,

Thank you a lot for your answer.

I'll try to implement your recommandation

Tx

Anonymous
Not applicable
Author

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
Not applicable
Author

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
Not applicable
Author

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