Pivot Table

[Anonymous]

Please find the below table and let me how can i achieve the my expression

Country	City	Count	Expression?
India	Hyd	1	Chennai+Mumbai+Delhi+New Jersey (14)
India	Chennai	2	Hyd+Mumbai+Delhi+New Jersey (13)
India	Mumbai	3	Hyd+Chennai+Delhi+New Jersey (12)
India	Delhi	4	Hyd+Chennai+Mumbai+New Jersey (11)
USA	New Jersey	5	Hyd+Chennai+Mumbai+Delhi (10)

If you observe my expression,always i have to exclude respective city for that row.

[MK9885]

I do not think it is possible to write such an expression with it gives you

+ with strings and number at the end.

It is possible to show one City per column but not a mix of all with +

If you really want that then use your last Column into Inline table or add a column next to count as you've done above, without numeric values.

ASFAIK it can;t be done, maybe someone with expertise level can guide you well?

[rupamjyotidas]

Simplest will be this

Sum(TOTAL Count) - Sum(Count)

[trdandamudi]

Please see the attachment for the solution and hope this helps..

[Anil_Babu_Samineni]

One more way this by using Set Analysis, But, This is not make sense any how. Hope getting some idea

=If(Count = 1, Sum({<City = {'Chennai','Delhi','Mumbai','New jersey'}>} TOTAL Count),

     If(Count = 2, Sum({<City = {'Delhi','Hyd','Mumbai','New jersey'}>} TOTAL Count),

          If(Count = 3, Sum({<City = {'Chennai','Delhi','Hyd','New jersey'}>} TOTAL Count),

               If(Count = 4, Sum({<City = {'Chennai','Hyd','Mumbai','New jersey'}>} TOTAL Count),

                    If(Count = 5, Sum({<City = {'Chennai','Delhi','Hyd','Mumbai'}>} TOTAL Count)

                      )

                  )

             )

       )

   )