topic Re: Sum expression in Table chart in QlikView

Sum expression in Table chart

Mon, 01 Oct 2012 14:56:03 GMT

Hello, I'm having trouble writing a expression for the next situation:

In a table chart, i have my periods (month - year), and the customers.

I want to add a column that have the percentage of sales by customer according to the total of sales o the month.

This is how I'm doing in:

- Add the two dimensions (period, client)

- Get the sales of the customer ( sum(Sales) )

- Get the total sales of the month ( sum({$<Client=>}Sales)

But the values i'm getting aren't the correct. The problem is getting the correct sum of sales of the month.

Any ideas of how to write the expression?

Thank you

Re: Sum expression in Table chart

swuehl — Mon, 01 Oct 2012 15:05:48 GMT

Try a TOTAL qualifer, like

=sum(TOTAL<Client> Sales)

edit: ah, sorry, you probably need

=sum(TOTAL<Month> Sales)

Re: Sum expression in Table chart

Mon, 01 Oct 2012 16:32:50 GMT

I added that, but i got the total of all periods, not the total per period.

Thanks

Re: Sum expression in Table chart

swuehl — Mon, 01 Oct 2012 17:24:24 GMT

I think this should work. You need to use the correct field name with the TOTAL qualifier, and field names are case sensitive in QV. If your dimension field is period, use

=sum(TOTAL<period> Sales)

=sum(Sales) / sum(TOTAL<period> Sales)

should give you the percentage of Sales for a given Client and given period compared with the total sales for that period across all Clients.

Re: Sum expression in Table chart

Mon, 01 Oct 2012 17:32:41 GMT

Yes, you're correct, it's working now, also i was using {} thinking it was set analysis.

Other question, this would work with dimensions created by expressions?

thank you

Re: Sum expression in Table chart

Mon, 01 Oct 2012 17:36:48 GMT

I mean, a calculated dimension.

Re: Sum expression in Table chart

swuehl — Mon, 01 Oct 2012 17:47:31 GMT

No, I think you are able to use field names only with the TOTAL qualifier.

If possible, create a field in the script to replace the calculated dimension with a field.