topic Re: set analysis total by dimension in QlikView

set analysis total by dimension

slribeiro — Mon, 10 Oct 2016 11:28:32 GMT

Hello.

I'm here with some issue in set analysis.

Imagine I want the following table.

ProductType	Country	Sales	Total Sales by Country
1	US	90	10000
1	China	150	15000
1	Australia	30	5000
1	Greece	100	40000
2	China	50	15000
2	Australia	20	5000
2	Greece	200	40000

To calculate Sales we use the regular sum({$<year={$(=max(year))}>} Sales), but I need help to calculate the Total Sales by Country.

The thing is to calculate something similar to sum({$<year={$(=max(year))}>} total Sales) but by country.

Thanks in advance.

Re: set analysis total by dimension

sunny_talwar — Mon, 10 Oct 2016 11:30:27 GMT

May be this:

Sum({$<year={$(=max(year))}>} TOTAL <Country> Sales)

Re: set analysis total by dimension

slribeiro — Mon, 10 Oct 2016 11:34:18 GMT

Yes, I've tried that and it works only if I use a straight table.

Do you have any ideia why that doesn't work in a pivot table?

best regards

Re: set analysis total by dimension

sunny_talwar — Mon, 10 Oct 2016 11:37:41 GMT

It should still work, would you be able to share a sample where this isn't working in a pivot table?

Re: set analysis total by dimension

slribeiro — Mon, 10 Oct 2016 12:08:45 GMT

What happens is that it only works if you have your pivot table fully expanded.

I attached a sample I've just made for you.

Re: set analysis total by dimension

sunny_talwar — Mon, 10 Oct 2016 12:12:22 GMT

What do you want your table to display when it isn't expanded?

Re: set analysis total by dimension

Anonymous — Mon, 10 Oct 2016 13:34:08 GMT

hi try this

aggr(rangemax(above(sum(sales)),0,rowno())),country)......for country wise sales

rangemax(above( TOTAL sum(sales)),0,rowno(TOTAL))) for consolidated sales...

Re: set analysis total by dimension

slribeiro — Mon, 10 Oct 2016 15:01:21 GMT

I this first situation all the values are correct because the second column has the total of sales for each country, but if you reduce or select one TypeProduct your values will be wrong as the ideia would be to maintain the same values above

Re: set analysis total by dimension

sunny_talwar — Mon, 10 Oct 2016 15:04:40 GMT

Try this:

Sum(Aggr(Sum({1<year={$(=max(year))}>} TOTAL <countrydesc> Sales), TypeProduct, countrydesc))

or you can use this:

Sum(Aggr(Sum({<year={$(=max(year))}, TypeProduct>} TOTAL <countrydesc> Sales), TypeProduct, countrydesc))