topic Re: Displaying Pivot table results in QlikView

Displaying Pivot table results

Mon, 22 Dec 2014 09:15:50 GMT

Hi all,

I have data in pivot table like below and I need to display in below hierarchy fashion only

Jan Feb Mar

Material Group Category Material Cost Cost Cost

MG1 C1 M1 100 100 100

C1 M2 200 300 200

C2 M3 300 400 500

while Drill down with Material Group the result should be: MG1 Jan| Feb| mar

600, 800,800

Jan | Feb | Mar

while Drill down with Material Group and Category results are : MG1 C1 300 400 300

C2 300 400 500

but I have a condition while displaying category Wise .

Example:if (category = C1 and period <201405,Sum(revenue) ,Sum(cost) ) .period is a dimension..Revenue is a key figure which is coming from different table and if the condition fails the results should aggregate as similar like above fashion..is it possible in pivot table

How should i achieve this ..

Thanks

kishore

Re: Displaying Pivot table results

Gysbert_Wassenaar — Mon, 22 Dec 2014 09:22:53 GMT

Enable the option Partial Sums for the dimensions and expand/collapse dimension values as needed.

Re: Displaying Pivot table results

Mon, 22 Dec 2014 09:32:07 GMT

Hi gwassenaar,

Can you please elaborate your solution and where should i find Expand/Collapse my dimension results in Pivot table

thanks for your Quick Response

Re: Displaying Pivot table results

Mon, 22 Dec 2014 09:38:08 GMT

gwassenaar

I need to show the Results for category wise based on the condition written in above.if condition fails results are displaying as similar like above exaple..

i was applying Partial sums options i am failed to get my expected results.

Re: Displaying Pivot table results

Gysbert_Wassenaar — Mon, 22 Dec 2014 09:43:11 GMT

The default setting for a pivot table is to show + and - icons that you can click on the expand that dimension value to show the details of the next dimension. You can enable/disable this by changing the Always Fully Expanded setting on the Presentation tab of the charts properties window.

Re: Displaying Pivot table results

Mon, 22 Dec 2014 09:49:26 GMT

I am displaying in the same fashion but my requirement to view the results on Category based on the Below Condition

Can you help me where should i apply this condition...Category is A dimension

Example:if (category = C1,C2.... and period <201405,Sum(revenue) ,Sum(cost) )

period is a dimension..Revenueis a key figure which is coming from different table and if the condition fails the results should aggregate as similar like above fashion..is it possible in pivot table

Thnks

Re: Displaying Pivot table results

Gysbert_Wassenaar — Mon, 22 Dec 2014 09:50:47 GMT

Please post a .qvw document that demonstrates the problem.

Re: Displaying Pivot table results

Mon, 22 Dec 2014 10:14:50 GMT

Hi,

unable to share my .qvw document i have no permissions to share .

I am giving the example based on my Actual requirement.

Re: Displaying Pivot table results

tyagishaila — Mon, 22 Dec 2014 10:27:59 GMT

Presently where are you using this condition?

if (category = C1 and period <201405,Sum(revenue) ,Sum(cost) )

use this condition in expression and allow Show Partial Sum in Presentation Tab for MaterialGroup.

Re: Displaying Pivot table results

Mon, 22 Dec 2014 10:37:48 GMT

Hi ,

Currently I am not using this condition anywhere ..i am displaying the results whatever the data available in my dataset

and Displaying like Collapse and Expand fashion based on Material group catgory etc..

Now i have a requirement to drill down Category dimension based on the above condition..where should i apply without effecting hierarchy fashion i.e(collapse and expand)...Revenue is coming from different table which is joined internally but currently not populating in my Pivot table

Thanks for your quick response

Re: Displaying Pivot table results

tyagishaila — Mon, 22 Dec 2014 11:01:06 GMT

according to your condition if Category = C1 and period <201405

then Sum(revenue)

else Sum(cost)

are these sums you want to show under cost field?

Re: Displaying Pivot table results

Mon, 22 Dec 2014 11:14:24 GMT

Hi Shaila,

yes exactly

with the same hierarchy fashion without effecting other dimension results..i.e if condition is fails ...

Re: Displaying Pivot table results

tyagishaila — Mon, 22 Dec 2014 11:30:40 GMT

Take Material Group, Category, material and Month as dimensions in pivot table,

write your condition in expression tab with label Cost.

and enable show partial sum.

I think result must be correct.