topic Re: Replicate excel pivot table in qlikview in QlikView

Replicate excel pivot table in qlikview

trdandamudi — Wed, 09 Mar 2016 20:07:13 GMT

I have a Pivot table format in Excel (Please see the attached .jpg file) and I would like to replicate same format in Qlikview pivot table. If I can replicate it in Qlikview then I can auto generate the reports into excel without any VBA coding on excel side.

The Low, Medium and High which are shown in the screen shot are not available in qlikview as dimension and I need to create them based on the below logic:

If New <=60 Then "Low"

If New > 60 and <=300 Then "Meduim"

If New >300 Then "High"

Can this be done and how..

Thanks

Re: Replicate excel pivot table in qlikview

sunny_talwar — Wed, 09 Mar 2016 20:37:48 GMT

Can you share the raw data to be able to replicate what you have in the image?

Re: Replicate excel pivot table in qlikview

trdandamudi — Wed, 09 Mar 2016 21:55:01 GMT

I am able to put together a sample of raw data for your use and here is the list below:

1) Pivot_Source_Data.xlsx

2) Pivot_Dashboard.qvw

3) Pivot_Output_Format.xlsx

Hope this helps...

Re: Replicate excel pivot table in qlikview

sunny_talwar — Thu, 10 Mar 2016 01:39:38 GMT

This?

Re: Replicate excel pivot table in qlikview

trdandamudi — Thu, 10 Mar 2016 01:59:09 GMT

I see that you created a field called "column' and then creating the pivot. In my case the data is dynamic and it should be calculated in the expressions, because the user will be selecting around 10 filters before exporting this pivot to Excel. Also at the total level I need to do averages. Because the data is confidential I created the source data in a simple table for this exercise. Do you think it can be done using Expressions and Dimensions on the fly...

I am trying to use the below formulas in the expressions and trying to get it into the format: ( I am able to get the values)

=Count (AA) <--------------- Items

=avg(Aggr(count(Used),AA,BB,CV,CD,GH)) <----------- Used

=avg(Aggr(Sum(New)/72,AA,BB,CV,CD,GH)) <-------------- New

But not able to replicate the formula in the dimension to bucket them as Low, Medium and High,,,,

Re: Replicate excel pivot table in qlikview

amayuresh — Thu, 10 Mar 2016 05:58:08 GMT

We can only show the table as per your requirement as,

As per my understanding it is not possible to fit your requirement in single pivot table. Means you want sum/count of Items, Used, New at High,Low, Medium level and Avg at subtotal level.

Re: Replicate excel pivot table in qlikview

sunny_talwar — Thu, 10 Mar 2016 10:50:00 GMT

So you are saying that New is not a number coming from the database, but a calculation that takes place on the front end of the application?

Would it be possible to share dummy data which matches the row format of your actual data?

Re: Replicate excel pivot table in qlikview

trdandamudi — Thu, 10 Mar 2016 13:44:45 GMT

That is correct..

I tried to put something together which is almost close to the original and hope this is what you are looking for:

1) Source_Data.xlsx

2) Pivot_Dashboard.qvw

3) Pivot_Output_Format_Ver_2.xlsx

Logic for Low/Medium/High:

If New <=60 Then "Low"

If New > 60 and <=100 Then "Meduim"

If New >100 Then "High"

In the dashboard I was able to get the low/medium/high as a Expression but want to see if we can get it as Dimension.

Thanks

Re: Replicate excel pivot table in qlikview

sunny_talwar — Thu, 10 Mar 2016 14:01:09 GMT

Check the attached

Calculated Dimension for High/Medium/Low:

=Aggr(If(avg(Aggr(Sum(MFrontTrips)/2,Detail_Descr,Place_Code,Place_Detail,Place,Num_ID,Alloc_Num)) <=60,'Low',

If(avg(Aggr(Sum(MFrontTrips)/2,Detail_Descr,Place_Code,Place_Detail,Place,Num_ID,Alloc_Num)) > 60 And avg(Aggr(Sum(MFrontTrips)/2,Detail_Descr,Place_Code,Place_Detail,Place,Num_ID,Alloc_Num)) <=100,'Medium',

If(avg(Aggr(Sum(MFrontTrips)/2,Detail_Descr,Place_Code,Place_Detail,Place,Num_ID,Alloc_Num)) > 100,'High'))),Detail_Descr,Place_Code,Place_Detail,Place,Num_ID,Alloc_Num)

Re: Replicate excel pivot table in qlikview

trdandamudi — Thu, 10 Mar 2016 14:41:11 GMT

Can we get the columns Low, Medium and High in order. Meaning the columns are showing as High, Medium, Low and it has to be Low, Medium, High.

Also as shown in the output format file, I need the Total columns to the right of the pivot table.

I think we are almost there....

Re: Replicate excel pivot table in qlikview

sunny_talwar — Thu, 10 Mar 2016 14:46:32 GMT

Use a sort expression for the Range Dimension:

=Match(Aggr(If(avg(Aggr(Sum(MFrontTrips)/2,Detail_Descr,Place_Code,Place_Detail,Place,Num_ID,Alloc_Num)) <=60,'Low',

If(avg(Aggr(Sum(MFrontTrips)/2,Detail_Descr,Place_Code,Place_Detail,Place,Num_ID,Alloc_Num)) > 100,'High'))),Detail_Descr,Place_Code,Place_Detail,Place,Num_ID,Alloc_Num),

'Low', 'Medium', 'High')

Re: Replicate excel pivot table in qlikview

trdandamudi — Thu, 10 Mar 2016 15:02:41 GMT

It is working .... Let me do few more tests because my data is very huge and I will get back to you today if there are any issues and then close this thread.

Thanks

Re: Replicate excel pivot table in qlikview

sunny_talwar — Thu, 10 Mar 2016 15:05:56 GMT

Sure thing.

Just note that with a huge data-set, performance is always going to be an issue with a calculated dimension with Aggr() and if() statements. I would recommend calculating them in the script if possible so that the front end experience improves for the users.

Re: Replicate excel pivot table in qlikview

trdandamudi — Fri, 11 Mar 2016 15:37:52 GMT

Thanks for your suggestions and I will look into it.

Able to test large volumes of data since yesterday and the code is working perfect. Thank you for your time and really appreciate it.

Thanks