Chart Piviot in Qlikview

[dunnalahk123]

Hi,

In Qlik view i have taken one Chart Box in that i used Pivot

So My Pivot have Three Dimensions

one is Region, Second is subregion and last is country.

One Expression.which gives me my distinct values.

So By Default it will be with region and it should take below expression

Sum(aggr(Sum({$<RegionFlag={$(vRegionFlag)}>} DISTINCT CSDEN ),[ASDPID],Region))

but  when i clik on + Sign and expand to subregion it should take below expression

Sum(aggr(Sum({$<RegionFlag={$(vSubregionFlag)}>} DISTINCT CSDEN ),[ASDPID],Subregion))

and when i click on one more time to expand country it shoudl take

Sum(aggr(Sum({$<RegionFlag={$(vCountryFlag)}>} DISTINCT CSDEN ),[ASDPID],Country))

is that possible to do it in Chart Piviot?

Or i created  a Cyclic Dimension by combing all 3 Region, subregion, COuntry

so when i click on subregion subregion expression should take and click on country country expression should take

is some way to do like that?

Best Regards,

HK

[vinieme12]

use Dimensionality() value

Example with Pick ()

Pick(Dimensionality() , Expression1 , Expression2 , Expression3)

With If()

if(Dimensionality() = 1 , Expression1 ,

(Dimensionality() = 2 , Expression2 ,

(Dimensionality() = 3 , Expression3)))

[dunnalahk123]

Hi,

Thank you.

Could You Please show me with sample Qvw if possible.

Best Regards,

HK

[vinieme12]

ideally you should be posting a sample!, but try the below

//LOAD Script//

LOAD * inline [

Country,State,City,Value,Value2,Value3

India,Maharashtra,Mumbai,1,2,3

India,Maharashtra,Pune,1,2,3

India,Maharashtra,Nasik,1,2,3

];

Create a Pivot table with Country,State,City as Dimension

Expression

=Pick(Dimensionality(),sum(Value),sum(Value2),sum(Value3))

[vinieme12]

if your query is resolved please close the thread by marking a response as correct, or post  a sample appQlik Community Tip: Marking Replies as Correct or Helpful

[dunnalahk]

Hi Vineeth,

Sorry in delay in Responding.

Thank You For all Suggestions . i am attaching sample QVW . at moment i splited Geography into different tabs and added in Container.

If possible i added one sheet with your name in attached qvw where you can help me.

Best Regards,

HK

[vinieme12]

for numerator

=Pick( Dimensionality(),

Sum(aggr(Sum({$<GlobalFlag={$(vGlobalFlag)}>} DISTINCT CSNUM ),[ASDPID],Global))   //When at Global level dimensionality value is 1

,

Sum(aggr(Sum({$<GlobalFlag={$(vGlobalFlag)}>} DISTINCT CSNUM ),[ASDPID],Region)) ////When at Region level dimensionality value is 2

,

Sum(aggr(Sum({$<GlobalFlag={$(vGlobalFlag)}>} DISTINCT CSNUM ),[ASDPID],RegionFlag))  //When at Global level dimensionality value is 3  and so on

,

Sum(aggr(Sum({$<GlobalFlag={$(vGlobalFlag)}>} DISTINCT CSNUM ),[ASDPID],Subregion))

,

Sum(aggr(Sum({$<GlobalFlag={$(vGlobalFlag)}>} DISTINCT CSNUM ),[ASDPID],Subregion1))

,

Sum(aggr(Sum({$<GlobalFlag={$(vGlobalFlag)}>} DISTINCT CSNUM ),[ASDPID],Country))

)

See how pick works

http://help.qlik.com/en-US/qlikview/12.1/Subsystems/Client/Content/Scripting/ConditionalFunctions/pi...

[vinieme12]

add an expression =Dimensionality() and expand each dimension to see it's dimensionality value, it'll be easier to understand