topic Re: Pivot problem for adding another dimension in QlikView

Pivot problem for adding another dimension

Anil_Babu_Samineni — Wed, 25 Nov 2020 16:16:04 GMT

Hello, I have simple logic to show in Pivot Table. I hope this is lot make sense. But, I got stuck there. I am sure, everyone seems this is easy but very difficult. We don't like even work around. Can we get any inputs from you one and all.

Coming to Task,

I have simple inline table, And then i have created two Pivot objects which one called Sum and another one called Average we can say and then I want to merge those into single along with Expectation

Hope clear, I've this. I remind you all i don't want any work around. In future, If i want to move that pivot to anyplace it should move all left of Text objects.

And I want this, From above i want to take Product wise for Avg Sales, For Ref attachment

Note: Please assist me, if we can do same thing in Qlikview. Ideas are more gain and i would love too

I've enclosed my file. Please work on that. If some one Don't have License please free to ask i will create one Word document for them

Best,

ANIL

Re: Pivot problem for adding another dimension

sunny_talwar — Fri, 16 Dec 2016 12:35:54 GMT

This?

Re: Pivot problem for adding another dimension

Anil_Babu_Samineni — Fri, 16 Dec 2016 12:49:12 GMT

Yes exactly this is which i am looking.

Here, I've some around 30 Expression. Do i need to use Pick with 30 Names You mean to say.

Till this is okay for sample, And i am happy with this answer

Now, I want to add few more after this like Set Analysis. I am confused whether we can do that or not. This scenario i am trying to add count Sales

PFA

Re: Pivot problem for adding another dimension

sunny_talwar — Fri, 16 Dec 2016 12:59:06 GMT

What is your Count Expression?

Count(Sales)?

Re: Pivot problem for adding another dimension

Anil_Babu_Samineni — Fri, 16 Dec 2016 13:01:31 GMT

Yes

like i need to add more than 30 expressions

And, I've trying to convenience to the user like if we use such kind of expressions in single object it will kills the performance. Even thought, He don't have patience to wait and convert mind. Haha, so finally she need same thing in one object only

Re: Pivot problem for adding another dimension

sunny_talwar — Fri, 16 Dec 2016 13:01:46 GMT

Like this?

Re: Pivot problem for adding another dimension

sunny_talwar — Fri, 16 Dec 2016 13:03:27 GMT

Look at the attached below

Re: Pivot problem for adding another dimension

Anil_Babu_Samineni — Fri, 16 Dec 2016 13:11:59 GMT

Again, Next expression seems this. I like it lot. And it works a charm. I assume, if we have 30 expressions do i need to add 30 Numbers in Inline Memory

How to write this?

And again i need to do adding the Dim values from script, Do you mean? I like your intend to see. Any other alternate we will do or this is the one which we enabled.

Dim

=Pick(Dim, 'Sum Sales', 'Avg Sales',Product,'Count Sales','Philips Data','Nokia Data')

Expression

Pick(Dim, Num(Sum(Sales), '#,##0'), Num(Avg(Sales)/1000, '##%'), Num(Avg(Sales)/1000, '##%'), Num(Count(TOTAL Sales), '#,##0'), Num(Sum({<Product = {'Philips'}>}Sales),'#,##0'), Num(Sum({<Product = {'Nokia'}>}Sales),'#,##0'))

Like this i need to add, 30 expression with 10 lines of set analysis for each selectors {Like Sum, Avg, Count, Philips Data, XXX, XXX, XXX}

Re: Pivot problem for adding another dimension

sunny_talwar — Fri, 16 Dec 2016 13:15:39 GMT

Yes, I think this is exactly what you will have to do

Re: Pivot problem for adding another dimension

Anil_Babu_Samineni — Fri, 16 Dec 2016 13:24:59 GMT

I like it, thanks. Do we expect any other work or shall i go a head and close the thread by using Flag.

Can you recommend for me few peoples who is interesting do on this. Can you tag few here. Then we can see or else i will go the same way as you suggested.

Thanks Talwar Bhai. I loved up lot to gain knowledge

Re: Pivot problem for adding another dimension

sunny_talwar — Fri, 16 Dec 2016 13:27:48 GMT

I learnt this from kush141087‌, not sure I know if there is any other way of doing it. But getting feedback from johnw‌ or marcowedel‌ won't harm. But these guys usually show up late evening EST. So, you are more than welcome to keep this thread open for their opinions

Re: Pivot problem for adding another dimension

Anil_Babu_Samineni — Sat, 17 Dec 2016 17:16:38 GMT

As of now, I've close the thread. If some one can help then we may use those into. Exploding is more gain

Thanks Sunny, adding few recommendations.

Re: Pivot problem for adding another dimension

johnw — Tue, 27 Dec 2016 22:42:03 GMT

Here are a couple approaches to what I think we're looking for without using to set analysis or calculated dimensions. Sheet1 just adds a dimension and sets it up like a regular pivot table, with the grand total collapsed since we don't care about product details for that. That's how I'd probably do it. Sheet2 is somewhat more complicated to set up the data for, but doesn't require us to collapse anything, and doesn't let the users expand or collapse.