How to exclude last selected dimension from string

[vikasmahajan]

Hi Team,

I am working on vizlib custom reporting ad-hoc reporting. There I have almost 72 dimensions I wanted to calculate market share based on user selected dimensions we are limiting up to 10 dimensions only using GetObjectField(0) function :

Created variable like vLvl1 =  subfield(If(Len(GetObjectField(0)) > 0, GetObjectField(0)) , ',' )

                                               vLvl2 = subfield(If(Len(GetObjectField(1)) > 0, GetObjectField(1)) , ',' )   .. Till vLvl10.

Now I wanted to use formula for market share calculation:

Sum({<New_Flag_YTD={1},MonthNumber={$(=$(vMaxMonth))}>} Sales_Dollars)
/Sum( TOTAL <MonthYear,Brand_Generic,Super_Channel,Channel> {<New_Flag_YTD={1},MonthNumber={$(=$(vMaxMonth))}>} Sales_Dollars)

Trying to create dynamic this portion <MonthYear,Brand_Generic,Super_Channel,Channel> after user doing pivotingwith  MonthYear field : it is gettting stored into var : $(vLvl4) .

My requirement is capture only   <$(vLvl1)&','&$(vLvl2)','&$(vLvl4)>   wanted to exclude Channel dimension how I can Achieve the same?

Please note:  I have vLvl1 to vLvl10 levels & user can select any level I always wanted to exclude last dimension.

Thanks

Vikas

[RafaelBarrios]

Hi @vikasmahajan 

i think you can try this

https://help.qlik.com/en-US/qlikview/May2022/Subsystems/Client/Content/QV_QlikView/ChartFunctions/Se...

for example

sum({$1<.............>} sales) 

will always do the calculation excluding your last selection.

Hope this helps.

Best,

help users find answers! Don't forget to mark a solution that worked for you & to smash the like button! 🤔

[vikasmahajan · Accepted solution]

Thanks for your reply. I ended with following formula:

=if(Len(GetObjectField(10)) >0 and Len(GetObjectField(9)) > 0 and Len(GetObjectField(8)) > 0 and Len(GetObjectField(7)) > 0 and Len(GetObjectField(6)) > 0 and Len(GetObjectField(5)) > 0 and Len(GetObjectField(4)) > 0 and Len(GetObjectField(3)) > 0 and Len(GetObjectField(2)) > 0 and Len(GetObjectField(1)) > 0 and Len(GetObjectField(0)) > 0, GetObjectField(0)&','&GetObjectField(1)&','&GetObjectField(2)&','&GetObjectField(3)&','&GetObjectField(4)&','&GetObjectField(5)&','&GetObjectField(6)&','&GetObjectField(7)&','&GetObjectField(8)&','&GetObjectField(10),
if(Len(GetObjectField(9)) > 0 and Len(GetObjectField(8)) > 0 and Len(GetObjectField(7)) > 0 and Len(GetObjectField(6)) > 0 and Len(GetObjectField(5)) > 0 and Len(GetObjectField(4)) > 0 and Len(GetObjectField(3)) > 0 and Len(GetObjectField(2)) > 0 and Len(GetObjectField(1)) > 0 and Len(GetObjectField(0)) > 0, GetObjectField(0)&','&GetObjectField(1)&','&GetObjectField(2)&','&GetObjectField(3)&','&GetObjectField(4)&','&GetObjectField(5)&','&GetObjectField(6)&','&GetObjectField(7)&','&GetObjectField(9),
if(Len(GetObjectField(8)) > 0 and Len(GetObjectField(7)) > 0 and Len(GetObjectField(6)) > 0 and Len(GetObjectField(5)) > 0 and Len(GetObjectField(4)) > 0 and Len(GetObjectField(3)) > 0 and Len(GetObjectField(2)) > 0 and Len(GetObjectField(1)) > 0 and Len(GetObjectField(0)) > 0, GetObjectField(0)&','&GetObjectField(1)&','&GetObjectField(2)&','&GetObjectField(3)&','&GetObjectField(4)&','&GetObjectField(5)&','&GetObjectField(6)&','&GetObjectField(8),
if(Len(GetObjectField(7)) > 0 and Len(GetObjectField(6)) > 0 and Len(GetObjectField(5)) > 0 and Len(GetObjectField(4)) > 0 and Len(GetObjectField(3)) > 0 and Len(GetObjectField(2)) > 0 and Len(GetObjectField(1)) > 0 and Len(GetObjectField(0)) > 0, GetObjectField(0)&','&GetObjectField(1)&','&GetObjectField(2)&','&GetObjectField(3)&','&GetObjectField(4)&','&GetObjectField(5)&','&GetObjectField(7),
if(Len(GetObjectField(6)) > 0 and Len(GetObjectField(5)) > 0 and Len(GetObjectField(4)) > 0 and Len(GetObjectField(3)) > 0 and Len(GetObjectField(2)) > 0 and Len(GetObjectField(1)) > 0 and Len(GetObjectField(0)) > 0, GetObjectField(0)&','&GetObjectField(1)&','&GetObjectField(2)&','&GetObjectField(3)&','&GetObjectField(4)&','&GetObjectField(6),
if(Len(GetObjectField(5)) > 0 and Len(GetObjectField(4)) > 0 and Len(GetObjectField(3)) > 0 and Len(GetObjectField(2)) > 0 and Len(GetObjectField(1)) > 0 and Len(GetObjectField(0)) > 0, GetObjectField(0)&','&GetObjectField(1)&','&GetObjectField(2)&','&GetObjectField(3)&','&GetObjectField(5),
if(Len(GetObjectField(4)) > 0 and Len(GetObjectField(3)) > 0 and Len(GetObjectField(2)) > 0 and Len(GetObjectField(1)) > 0 and Len(GetObjectField(0)) > 0, GetObjectField(0)&','&GetObjectField(1)&','&GetObjectField(2)&','&GetObjectField(4),
if(Len(GetObjectField(3)) > 0 and Len(GetObjectField(2)) > 0 and Len(GetObjectField(1)) > 0 and Len(GetObjectField(0)) > 0, GetObjectField(0)&','&GetObjectField(1)&','&GetObjectField(3),
if(Len(GetObjectField(2)) > 0 and Len(GetObjectField(1)) > 0 and Len(GetObjectField(0)) > 0, GetObjectField(0)&','&GetObjectField(2),
if(Len(GetObjectField(1)) > 0 and Len(GetObjectField(0)) > 0, GetObjectField(1),
If(Len(GetObjectField(0)) > 0 ,GetObjectField(0)
,0)))))))))))

Vikas 

[vinieme12]

you can use valueloop() to somewhat simplify this

example 

=$(=concat(distinct 'GetObjectDimension('&ValueLoop(0,9)&')',','))

[vikasmahajan]

Vini Thanks Let me try