topic Re: Pivot table with distinct values and values per dimension in QlikView

Pivot table with distinct values and values per dimension

Thu, 25 Apr 2013 09:13:18 GMT

Hi. I'm having trouble with making a table. I've run into this several times, and wonder if any of you have a sollution.

The table you see here is what I'm trying to do.

Note that it is a pivot table, where the customer can use the [+] to drill down to Country, some cyclic group selections, projects etc.

orderamount	YYMM 201301	YYMM2 201302	YYMM 201303
SUM(Orderamount)	SUM(Margin per month)	SUM(Margin per month)	SUM(Margin per month)
This should be a distinct value

Ofcourse I have no problems with putting the dimensions there and getting the formule SUM(Margin per month) to work.

The problem for me is the SUM(Orderamount). Which should only be shown once. (There are actually 8 such formula which should be distinct, and not per month).

How do I do this? What is the correct formula to get the sum(Orderamount) to only show once. Keeping in mind that there is a cyclic group and such.

(Also, if I have to let that part go, that's ok. But then I still can't get it to work).

Thanks!

Re: Pivot table with distinct values and values per dimension

er_mohit — Thu, 25 Apr 2013 09:22:39 GMT

use aggr function on the basis of that you got orderamount

aggr(sum(distinct Orderamount),basedfieldname)

Re: Pivot table with distinct values and values per dimension

sushil353 — Thu, 25 Apr 2013 09:25:50 GMT

Please post the sample application

Re: Pivot table with distinct values and values per dimension

Thu, 25 Apr 2013 10:25:51 GMT

Hereby you receive the sample application as per your request.

Re: Pivot table with distinct values and values per dimension

Thu, 25 Apr 2013 10:26:50 GMT

But how will that work with Cyclic groups?

Also, I have to name all the dimensions right?

I've also upload an example file in another post just down...