Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi team,
i am using a set analysis where i have to call a function in set analysis. as per my understanding the function will return the REGION which is associated with that particular STATE dimension and according to that condition calculate the T/O
The green mark is the REGION which is coming to each state.
the same REGION i have to use in calulating the red marked column.
i have written below mention syntax
Sum({<MEMBER_REGION={'$(=MAXSTRING(REGION))'}>} TRADE_VALUE)/sum(TRADING_DAYS)/100000
THE ISSUE ID OBSERVE THAT ITS USING 'WR' REGION IN ALL THE STATE BUT ALL STATE SHOULD BE CALCULATED ON BASE ON WHICH REGION IS ASSOCIATED TO IT.
Kindly help me how to resolve this
you will not like this
another way would be to hard-code the regions in your set analysis expression, something like :
if(region='IN', Sum({<MEMBER_REGION={'IN'}>} TRADE_VALUE)/sum(TRADING_DAYS)/100000,
if(region='MO', Sum({<MEMBER_REGION={'MO'}>} TRADE_VALUE)/sum(TRADING_DAYS)/100000,
........ etc.......
Not very dynamic i am afraid....
You are trying to apply set analysis per row which is not possible. Set Analysis is calculated on a chart level not per row.
But try this, maybe it works :
Sum({<MEMBER_REGION={'$(=Only(REGION))'}>} TRADE_VALUE)/sum(TRADING_DAYS)/100000
Hi,
Simply use the region as dimension and in presentation tab hide that dimension.
HTH
Sushil
Thanks.
But that is also not working...
there is difference between MEMBER_REGION and REGION.
REGION is associated to STATE like ( WB,Assam,Orissa is part of ER ( Eastern Region)
MEMBER_REGION is associated to MEMBERS.
SO if any MEMBER is associated to ER then he is native members of ER REGION and so on.
Thus i have to calculated Native T/O(lakh) and NON Native T/O(Lakh) any MEMBER can place trade from anywhere.
There is any other way to achieve ??
you will not like this
another way would be to hard-code the regions in your set analysis expression, something like :
if(region='IN', Sum({<MEMBER_REGION={'IN'}>} TRADE_VALUE)/sum(TRADING_DAYS)/100000,
if(region='MO', Sum({<MEMBER_REGION={'MO'}>} TRADE_VALUE)/sum(TRADING_DAYS)/100000,
........ etc.......
Not very dynamic i am afraid....
You can use a sum(If()) as set analysis will not work as explained above:
Sum(Aggr(If(MEMBER_REGION = MaxString(REGION), TRADE_VALUE)), State) / Sum(TRADING_DAYS) / 100000
Otherwise, you could change the load script to add a flag , but without knowing the full details of your data model it is hard to be specific. Then you could use the flag like
Sum({<Flag = {1}>} TRADE_VALUE) / Sum(TRADING_DAYS) / 100000
If each state has only a single REGION, then I would replace MaxString(REGION) with Only(REGION) in my expression above
i had not tried yet.. but will try later.
Thanks