Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
i think you can try this
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! 🤔
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
you can use valueloop() to somewhat simplify this
example
=$(=concat(distinct 'GetObjectDimension('&ValueLoop(0,9)&')',','))
Vini Thanks Let me try