Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Save the Date: QlikWorld Online, June 24-25, 2020. Free global virtual event for data integration and data analytic gurus. Register Today
Highlighted
Honored Contributor II

Replicate excel pivot table in qlikview

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

1 Solution

Accepted Solutions
Highlighted

Re: Replicate excel pivot table in qlikview

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)) > 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),

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


Capture.PNG

View solution in original post

13 Replies
Highlighted

Re: Replicate excel pivot table in qlikview

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

Highlighted
Honored Contributor II

Re: Replicate excel pivot table in qlikview

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...

Highlighted

Re: Replicate excel pivot table in qlikview

This?

Capture.PNG

Highlighted
Honored Contributor II

Re: Replicate excel pivot table in qlikview

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,,,,

Highlighted
Contributor III

Re: Replicate excel pivot table in qlikview

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

a.PNG

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.

Highlighted

Re: Replicate excel pivot table in qlikview

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?

Highlighted
Honored Contributor II

Re: Replicate excel pivot table in qlikview

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

Highlighted

Re: Replicate excel pivot table in qlikview

Check the attached

Capture.PNG

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)

Highlighted
Honored Contributor II

Re: Replicate excel pivot table in qlikview

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....