Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Qlik Analytics Tour 2020 Online. Begins August 10th. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator II
Creator II

Chart Piviot in Qlikview

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


7 Replies
Highlighted
Champion II
Champion II

Re: Chart Piviot in Qlikview

use Dimensionality() value

Example with Pick ()

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

With If()

if(Dimensionality() = 1 , Expression1 ,

(Dimensionality() = 2 , Expression2 ,

(Dimensionality() = 3 , Expression3)))

Highlighted
Creator II
Creator II

Re: Chart Piviot in Qlikview

Hi,

Thank you.

Could You Please show me with sample Qvw if possible.

Best Regards,

HK

Highlighted
Champion II
Champion II

Re: Chart Piviot in Qlikview

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

Highlighted
Champion II
Champion II

Re: Chart Piviot in Qlikview

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

Highlighted
Contributor III
Contributor III

Re: Chart Piviot in Qlikview

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

Highlighted
Champion II
Champion II

Re: Chart Piviot in Qlikview

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

Highlighted
Champion II
Champion II

Re: Chart Piviot in Qlikview

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