Replicate excel pivot table in qlikview

[trdandamudi]

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

[sunny_talwar · Accepted solution]

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

[trdandamudi]

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

[sunny_talwar]

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.

[trdandamudi]

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