topic Re: Pivot Table in QlikView

Pivot Table

tmumaw — Mon, 05 Jun 2017 16:43:35 GMT

Okay group here is an easy one for you all. I have pivot table that is broken down by GL Hier 3 I want to compare it to gross sales. How do I get the pivot table to ignore the dimension and just put in total gross sales. I have created a summary table with just gross sales by profit center and year.

Re: Pivot Table

vishsaggi — Mon, 05 Jun 2017 17:08:12 GMT

What is the expression you used and what is your expected output in Gross Sales column. ?

May be like:

= Sum({<GLHeir3=>} Aggr(Sum({<GLHeir3=>}Sales), ProfitCenter, Year))

Re: Pivot Table

tmumaw — Mon, 05 Jun 2017 18:00:32 GMT

I created a link key made up of Profit Center and Date and then I summed the total gross sales amount. For some reason the Gross Sales amount is still blank. GLPCA is the detail table.

Re: Pivot Table

ahaahaaha — Tue, 06 Jun 2017 08:26:24 GMT

You need to analyze the load script. Something is wrong with joining the SalesVIP table. I think, to help you, need the fragment of your source data and script code.

Re: Pivot Table

tmumaw — Tue, 06 Jun 2017 11:00:51 GMT

This calculation did not work. I am trying to find out what expenses are to gross sales. The column labeled Gross sales should be the same on every line and ignore the dimension GL Hier 3. The second image shows gross sales only showing up in one GL Hier 3 row. However I need it to show up in all rows to do the calculation % to Gross

Re: Pivot Table

tmumaw — Tue, 06 Jun 2017 11:57:13 GMT

Andrey I need to get Gross Sales on every line to do the % to gross calculation. Any ideas? Fixed had to use the following:

sum(TOTAL <FISYR> {<[GL Hier 3] = {'Gross Sales'}>}[In company code currency_HSL] * -1)

Re: Pivot Table

ahaahaaha — Tue, 06 Jun 2017 12:54:36 GMT

May be approach to the solution as in attached file.

Re: Pivot Table

tmumaw — Tue, 06 Jun 2017 13:20:42 GMT

Thanks for your help Andrey. Have a great day.

Re: Pivot Table

chaper — Tue, 06 Jun 2017 13:28:03 GMT

Did you tried with something like this Sum({<GLHeir3=>}Total Sales)