topic Re: How to create this pivot ? in QlikView

How to create this pivot ?

Fri, 03 Apr 2015 08:55:22 GMT

Hello,

I am trying to create the following pivot

Basically, there is a period selector which is used to Select Jan - 15, and the bands on the left (in red) are the aging bands.

I want to represent that there are 73 people who were in the band 11 - 20 in jan-15 moved to 0 - 10 in Feb-15 and 82 in 0 - 10 in mar - 15

I am trying to show if there is a pattern in movement between bands across periods

Is this even possible ?

Please advise.

Thanks

Re: How to create this pivot ?

MK_QSL — Fri, 03 Apr 2015 09:07:07 GMT

Without looking your data it's difficult to give you answer. Can you provide some sample dummy data in excel file to work?

Re: How to create this pivot ?

hariprasadqv — Fri, 03 Apr 2015 09:33:55 GMT

Hi,

As Manish said, dummy data required for the related suggestion and discussion.

As per my understandability of the scenario, it is possible to do it with class() function, which will creates regular buckets like 1-10,11-20...etc, use count() function along with that for counting the number of values which fall under that bucket.

The fallowing thread may help you... Lavanyar

Function CLASS

Thanks

Re: How to create this pivot ?

Fri, 03 Apr 2015 10:41:38 GMT

I have already created the buckets using class.

The problem I have is to display the buckets (same dimension) as two different dimensions across the pivot.

Attached is sample data and what I wish to achieve is on the right side tabulated form.

I cannot share the actual data as its sensitive information.

Re: How to create this pivot ?

sunny_talwar — Fri, 03 Apr 2015 12:35:40 GMT

Is this close to what you wanted? PFA

Best,

Sunny

Re: How to create this pivot ?

petter — Fri, 03 Apr 2015 12:44:15 GMT

My version of it

By the way this could be an excellent candiate for a Scatter Chart / Bubble Chart visualization too...

Re: How to create this pivot ?

Fri, 03 Apr 2015 12:45:24 GMT

Hello Sunny,

Thank you very much for the idea. Yes this is close.

But in my case, I am creating the buckets on the fly by taking a user input so I cannot replicate the Month or the Bucket dimension in the table.

Also, the case above the user input for month was fixed to 201501 but in the application it will be based on a list box selection. So they can choose 201502 and I should show all remaining months i.e. 201501 and 201503 .

I was trying to create a pivot table with two dimensions (of different alternate states) but that doesn't seem to work

Re: How to create this pivot ?

sunny_talwar — Fri, 03 Apr 2015 12:48:17 GMT

Your second concern can definitely be addressed. Instead of using static value for the if statement I can use variables which are driven by the user's selection in list box.

To your 1st point. What bucket is user's input?

Best,

Sunny

Re: How to create this pivot ?

sunny_talwar — Fri, 03 Apr 2015 12:53:22 GMT

Please find another version which is dynamic for user selection on the Bill Month. PFA

Best,

Sunny

Re: How to create this pivot ?

Fri, 03 Apr 2015 13:14:03 GMT

Well, both of them are age buckets which are derived from the same field in the table - aging days.

So, essentially its a bucket created with a class function

class(sum(aging_days),v_age_bucket) where v_age_bucket is from an input box.

Re: How to create this pivot ?

Fri, 03 Apr 2015 13:15:24 GMT

Petter,

Yes you are right, the ultimate objective is to present this as a scatter chart - but unfortunately I cant even get the pivot to work

Re: How to create this pivot ?

sunny_talwar — Fri, 03 Apr 2015 13:19:06 GMT

In that case you can use a value list function for dimension. I think that will suit your needs perfectly. Get user input, create dimension values in a variable and use that within the value list function.

If you can give me a sample input to work with, I might be able to produce something for you. What I am unsure is how does SUB_ID gets linked to the user input? So a sample would be able to help here.

Best,

Sunny

Re: How to create this pivot ?

Fri, 03 Apr 2015 13:34:28 GMT

Let me try and scramble data and send it - may take while

Meanwhile, ValueList cannot be used over an aggr function I suppose ?

My actual code to get the Buckets is below - v_mou_int is the user input variable

Aggr(Class(sum({Orig_Days),v_mou_int,'min'),month_year,CO_ID,Service)

Re: How to create this pivot ?

sunny_talwar — Fri, 03 Apr 2015 13:36:55 GMT

I am thinking of using the Aggr in the variable which creates a static list of values for the ValueList function. I think what you are referring to is that Aggr (as an expression) doesn't work with the ValueList function which is definitely true.

HTH

Best,

Sunny

Re: How to create this pivot ?

sunny_talwar — Fri, 03 Apr 2015 13:37:33 GMT

Take your time, I am in no hurry today 😉

Re: How to create this pivot ?

Fri, 03 Apr 2015 15:34:33 GMT

Here is the sample data.

Basically, I have the table which I have chosen 1 sub Id and put them in different buckets in the various months.

So what I would like to achieve is what I described above.

I am going to try using the variable for aggr expression and use that in ValueList now..

Re: How to create this pivot ?

sunny_talwar — Fri, 03 Apr 2015 15:35:36 GMT

I am going to try the same thing at my end...

Best,

Sunny

Re: How to create this pivot ?

sunny_talwar — Fri, 03 Apr 2015 15:54:48 GMT

Understanding the data would take some time, but I kind of understood what you wanted here. Not sure if you really need ValueList in your case. See if this new updated sample makes sense for you.

Best,

Sunny

Re: How to create this pivot ?

Fri, 03 Apr 2015 16:21:44 GMT

I understand the way to create the dynamic buckets by creating two dimensions - but the month still seems to elude me

How about I create two list boxes with alternate states and use them ?

EDIT: Just realized you have already done this

Re: How to create this pivot ?

sunny_talwar — Fri, 03 Apr 2015 16:43:46 GMT

So have we reached the final answer yet??? Not sure if we have or do we still need to work on it

Best,

Sunny