topic Re: Pivot Table Question Qlikview in QlikView

Pivot Table Question Qlikview

Davidb69 — Sat, 16 Nov 2024 03:11:11 GMT

So I am pretty new to Qlikview(I was introduced last year and we started development this year). There is one issue on one report that I can't seem to escape. When I am doing a Pivot table, I am looking to count the number of Orders each customer has made during a time frame by towns.

So if John lives in Philly and made 2 orders,

Bob lives in Pittsburgh and made 1 order

Sue also lives in Pittsburgh and made 1 order

in my Pivot table I am currently seeing this

Town Orders Customers

Philadelphia 2 1

Pittsburgh 1 2

I'd like to see 1 or more or 2 or more etc. This way I know how many orders were made in each town.

So it should look like this

Town Orders Customers

Philadelphia 1 1

Philadelphia 2 1

Pittsburgh 1 2

In the pivot table dimensions

I have TOWN and

for the orders I have a calculated Dimension

=AGGR(COUNT(DISTINCT ORDERNUM), CUSTACCT)

In expressions I have

=rangesum(above(count(distinct CUSTACCT),0,50))

Thanks in advance for the assistance.

Re: Pivot Table Question Qlikview

sunny_talwar — Wed, 26 Jun 2019 11:16:07 GMT

Your output requirement is not very clear... would you be able to elaborate a little on this?

Re: Pivot Table Question Qlikview

Davidb69 — Wed, 26 Jun 2019 11:42:35 GMT

What I am trying to gain here, is I want to know how many customer reorders there are by Town.

So I want to know 1 and more, 2 and more, 3 and more, 4 and more etc.

Issue right now is if there is no 1 order but there are 2+, nothing would populate in that column giving the total of the 1 and more orders an incorrect total.

Re: Pivot Table Question Qlikview

sunny_talwar — Wed, 26 Jun 2019 11:52:07 GMT

Still not 100% sure I understand... Table1

Is the Orders field here Count of orders?

And this one it the actual order? How is Philly getting divided into two?

Re: Pivot Table Question Qlikview

Davidb69 — Wed, 26 Jun 2019 13:45:23 GMT

Yes, Philly would have 2 rows, one show 1 and more orders and one showing 2 and more orders.

Re: Pivot Table Question Qlikview

sunny_talwar — Wed, 26 Jun 2019 13:55:46 GMT

I don't see how... Can you share the raw data behind this output?

Re: Pivot Table Question Qlikview

Davidb69 — Wed, 26 Jun 2019 14:42:16 GMT

No problems, I can replicate in this excel sheet to help you understand a bit more.

Main data is a snipet of actual data from orders for this year in certain areas.

The first pivot gives me the number of orders per town/customer

Final pivot shows the number of customers and how many they ordered and

K4:R9 is the expected output from QLIKVIEW. Items in Yellow are totally missing right now as it's programmed.

Thank you.

Dave