Count Issue

[#1]

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.

[fosuzuki]

Hi,

check the attached sample.

Hope this helps

Fernando

[sebastiandperei]

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.

[#4]

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... 

[fosuzuki · Accepted solution]

Check the attached qvw!

[#6]

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

[fosuzuki]

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.

[#8]

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

[fosuzuki]

Check the doc

[sebastiandperei]

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