topic Re: Set Analysis in Pivot table in QlikView

Set Analysis in Pivot table

Anonymous — Fri, 17 Mar 2017 11:02:41 GMT

Hi All,

Can we use set analysis to read the value of the specific row ??

I mean if my rows dimension in table is KPI, can i get the value of the KPI2 using set analysis??

Please help

Re: Set Analysis in Pivot table

sunny_talwar — Fri, 17 Mar 2017 11:04:26 GMT

I am not sure I understand, can you share an example?

Re: Set Analysis in Pivot table

Anil_Babu_Samineni — Fri, 17 Mar 2017 11:05:34 GMT

Can you describe more with data and condition

KPI has vwhich values(I am assume, A B C D). And then which row value you want to took

Sum({<KPI = {'A'}>}Sales) // This will give only KPI of A. That's called Row wise i am assumption

Re: Set Analysis in Pivot table

Anonymous — Fri, 17 Mar 2017 11:15:59 GMT

I have a pivot table with shows KPIs(A, B, C) at various month(Jan, Feb, Mar) level. (KPIs are rows and Months are Columns).

I need to insert an extra row which should display the value of KPI1/KPI2 for each month.

Can I achieve this using set analysis?

Sum({<KPI = {'A'}>}Sales) filters the rows and displays only record where KPI='A'.

I need the records for KPIs A, B, C to be as it is and insert an extra record "ratio" at the bottom.

Please find the excel attached.

Re: Set Analysis in Pivot table

sunny_talwar — Fri, 17 Mar 2017 11:21:46 GMT

Check the attached

Re: Set Analysis in Pivot table

Anonymous — Fri, 17 Mar 2017 11:40:26 GMT

Thanks for your answer Sunny. But i also want the YTD in the columns. Can't we put pick in both Rows in Columns??

Re: Set Analysis in Pivot table

sunny_talwar — Fri, 17 Mar 2017 11:43:33 GMT

Check this now

Re: Set Analysis in Pivot table

Anonymous — Fri, 17 Mar 2017 12:19:30 GMT

Thank you Sunny. It worked very well.

But in the same way, can we get the ratio of BU to that of total Jan in the KP1.

something like sum({<KPI={"Kp1"}>}Value)/sum({<KPI={"Kp1"},BU=>}Value) so that we can get ratio like 20/(20+20) =0.5.

Re: Set Analysis in Pivot table

sunny_talwar — Fri, 17 Mar 2017 12:21:10 GMT

I am not sure what you are looking to get.... can you share the expected output in an excel?

Re: Set Analysis in Pivot table

Anonymous — Fri, 17 Mar 2017 12:59:15 GMT

Please find the excel.

Re: Set Analysis in Pivot table

sunny_talwar — Fri, 17 Mar 2017 13:04:53 GMT

Check now

Re: Set Analysis in Pivot table

Anonymous — Fri, 17 Mar 2017 13:15:00 GMT

Thank you so much for you time and patience Sunny.

Re: Set Analysis in Pivot table

sunny_talwar — Fri, 17 Mar 2017 13:15:32 GMT

No problem at all

Re: Set Analysis in Pivot table

Mon, 20 Mar 2017 09:34:36 GMT

Hi Sunny,

Is there any way to sort these KPIs, as we want. For example Ratio should come below KPI1 ?

Re: Set Analysis in Pivot table

sunny_talwar — Mon, 20 Mar 2017 09:52:14 GMT

You can do this using match function for your sorting expression

Re: Set Analysis in Pivot table

Mon, 20 Mar 2017 10:03:16 GMT

I have tried using Match(KPI,'KPI1','Ratio','KPI2'), this is not working as Ratio is not in KPIs list.

Re: Set Analysis in Pivot table

sunny_talwar — Mon, 20 Mar 2017 11:00:09 GMT

You need to use the complete dimension instead of just KPI

=Match(Pick(Dim, KPI, 'Ratio'), 'KP1','Ratio','KP2', 'KP3')

Re: Set Analysis in Pivot table

Mon, 20 Mar 2017 11:58:53 GMT

Thank you Sunny. It is working as expected now

Re: Set Analysis in Pivot table

sunny_talwar — Mon, 20 Mar 2017 12:12:15 GMT

No problem

Re: Set Analysis in Pivot table

Anonymous — Tue, 21 Aug 2018 05:12:35 GMT

Hi Sunny,

I Have a list of projects with Start & End dates.

I have used a master calendar to break it down into WK & Year months etc.

Now on the Pivot chart, I want o display the whole lost on MonthYear & Week but starting from the current week.

Which means I am not interested in the past weeks on the project I am looking at.

I need to look at the MonthYear from July 2018 & present week.

Please can you help me.