Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us in Toronto Sept 9th for Qlik's AI Reality Tour! Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
ali_hijazi
Partner - Master II
Partner - Master II

Identify which dimension is at each row level

Hello I got the below pivot table:

ali_hijazi_1-1742895632466.png

the measure num_org is a simple sum with conditions in set analysis
the second measure denum_org is avg( aggr (sum(), Contract Type))

but since there are several dimensions to be selected and expanded what I want is the following:
for the row at the level of the Contract type I want the denum_org to be:
avg( aggr (sum(), Contract Type))
while for the row at the level of the contract :
avg( aggr (sum(), [Contract Type],Contract))

and so on
and also if the user swaps the dimensions I want the aggregation by to be based on the dimension which is at the level of the row

so if the user swaps Contract Type with Region then the expression at the level of the region should be:
avg( aggr (sum(), Region))

 

so how can I identify dynamically the dimension in the row of the pivot table if I"m at the level of Contracct type, Contract, or region,...

ali_hijazi_0-1742896115106.png

here if I want to get the result by contract I need to add Contract to the aggregation so the expression is 
avg(aggr(sum(), Contract type, Contract))

However on the row at the level of "Internal" I want the calculation to be avg(aggr(sum(),Contract Type)) to keep the original value 5%

hope I clearly explained the issue
Kindly advise

 

I can walk on water when it freezes
Labels (3)
1 Solution

Accepted Solutions
ali_hijazi
Partner - Master II
Partner - Master II
Author

what I did is the following:
I put my expression in a variable to which I made the aggregation by column parameteric 
and the expression in the chart looks like this and it works well

pick(wildmatch(GetObjectDimension(Dimensionality()-1)
,'Contract Type','Contract','act division','act department','act name','region','entity mgt','resource full name')
,$(vL.Rates.Denom.Group.By([Contract Type]))
,$(vL.Rates.Denom.Group.By([CNT]))
,$(vL.Rates.Denom.Group.By([ACT Division]))
,$(vL.Rates.Denom.Group.By([ACT Department]))
,$(vL.Rates.Denom.Group.By([ACT Name]))
,$(vL.Rates.Denom.Group.By([Region]))
,$(vL.Rates.Denom.Group.By([Entity MGT]))
,$(vL.Rates.Denom.Group.By([Resource Full Name]))

and the variable is as per below:
avg(
aggr
(
sum(
{
<
Scenario={[actual]}
,NMY={[$(vL.UpToDate)]}
,AGGREGATION_TYPE={ytd}
,MONTH_DIFF={$(vL.CY.MonthDiff.Actual.DeNumer.Range)}
,HCTYPE={[HC]}

>
}
HC)
,$1,[%PERIOD]))

I can walk on water when it freezes

View solution in original post

4 Replies
AshutoshBhumkar
Partner - Specialist
Partner - Specialist

Hi, Check if you can use Dimensionality() function, which will help you to identify each row separately.

 

Thanks,

Ashutosh

ali_hijazi
Partner - Master II
Partner - Master II
Author

dimensionality() helps me identify the level 1,2,3,...
what I want is to identify the dimension name so that I aggr by it 

I can walk on water when it freezes
Chanty4u
MVP
MVP

Not tested give a try 

Avg(Aggr(Sum(num_org), $(=GetObjectField(Dimensionality()))))

 

ali_hijazi
Partner - Master II
Partner - Master II
Author

what I did is the following:
I put my expression in a variable to which I made the aggregation by column parameteric 
and the expression in the chart looks like this and it works well

pick(wildmatch(GetObjectDimension(Dimensionality()-1)
,'Contract Type','Contract','act division','act department','act name','region','entity mgt','resource full name')
,$(vL.Rates.Denom.Group.By([Contract Type]))
,$(vL.Rates.Denom.Group.By([CNT]))
,$(vL.Rates.Denom.Group.By([ACT Division]))
,$(vL.Rates.Denom.Group.By([ACT Department]))
,$(vL.Rates.Denom.Group.By([ACT Name]))
,$(vL.Rates.Denom.Group.By([Region]))
,$(vL.Rates.Denom.Group.By([Entity MGT]))
,$(vL.Rates.Denom.Group.By([Resource Full Name]))

and the variable is as per below:
avg(
aggr
(
sum(
{
<
Scenario={[actual]}
,NMY={[$(vL.UpToDate)]}
,AGGREGATION_TYPE={ytd}
,MONTH_DIFF={$(vL.CY.MonthDiff.Actual.DeNumer.Range)}
,HCTYPE={[HC]}

>
}
HC)
,$1,[%PERIOD]))

I can walk on water when it freezes