topic Re: Count Issue in QlikView

Count Issue

Wed, 22 Aug 2012 21:23:07 GMT

Hi,

my date looks like below

Customer Old Grade New Grade

ABC 1 2

DEF 1 2

XYZ 1 3

MNO 1 4

Pqr 2 1

so on....

now my issue is I need a pivot table with columns old grade, new grade and count of customers where grading has been switched from old grade to new grade..

my result should look like

New Grade

1 2 3 ............

old grade 1 2 -------->( here count 2 is there are two customers (ABC,DEF) whose grading has been moved from 1 to 2).

Plesase note : I'm ok if the results can be achieved using anyother chart.

Any help is highly appreciated.

Re: Count Issue

fosuzuki — Wed, 22 Aug 2012 22:15:41 GMT

Hi,

check the attached sample.

Hope this helps

Fernando

Re: Count Issue

sebastiandperei — Thu, 23 Aug 2012 02:52:08 GMT

Hi!

In addition to Fernando's solution, i think that the expression must be

Count(If([New Grade]<>[Old Grade], Customer))

Because, if you don't put the "If", it will show the Customers that has NewGrade=OldGrade( for example NewGrade=2, and OldGrade=2 ).

Like this way, you will see only the customers that changes their Grade.

In this example, it looks equivalent, because you don't have Customers that haven´t changed.

Re: Count Issue

Thu, 23 Aug 2012 14:25:54 GMT

Hi Suzuki

Your solution worked perfectly. To add twist in the tail I need percentage change also..

numerator should be..count of customer ids where old grade has been swithced to new grade (which I got through your solution

denomiator should be count of customer of the orginal grade..

example :

if there are 100 customers with old grade 1 out of which 30 have assigned New grade 2. 60 to new grade 2. and 10 to new grade 3

then I need something like this..

new grade

1 2 3 4

Old grade 1 - 30/100 60/100 10/100

Any help is highly appreciated...

Re: Count Issue

fosuzuki — Thu, 23 Aug 2012 15:00:03 GMT

Check the attached qvw!

Re: Count Issue

Thu, 23 Aug 2012 15:48:16 GMT

Thanks a ton Fernando it worked..but quick question but how did you align new grade in row..did you do anything special my pivot table shows as column..beside old grade..i'm on version 10

Re: Count Issue

fosuzuki — Thu, 23 Aug 2012 16:24:58 GMT

When you create a pivot table, by default, it will put each dimension in a different column, just like a straight table. But then, you can drag the column to the top right side of the object and then it will change to row-style.

Re: Count Issue

Thu, 23 Aug 2012 17:11:46 GMT

I'm afraid I can't do it...can you please share a scrrenshot....

Re: Count Issue

fosuzuki — Thu, 23 Aug 2012 18:02:26 GMT

Check the doc

Re: Count Issue

sebastiandperei — Thu, 23 Aug 2012 21:46:53 GMT

If you have customers that never has changed their grade, this expression will count it as they has changed.

Re: Count Issue

Mon, 27 Aug 2012 18:57:02 GMT

Thank You Fernando.