Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I want to transform the below set expression into a short one and more dynamic. There are multiple if(Dmensionality()=1 and Region=......) Please suggest something dynamic
Is it possible to short this expression ?
***********************************************************************************************************
if(Dimensionality()=1 and Region='NAM',Sum(Sales)+Sum({<Region={'NAM'},Country={'Tyx'}>} total Sales),
if(Dimensionality()=1 and Region='APAC', Sum(Sales)+ Sum({<Region={'APAC'},Country={'Tyx'}>} total Sales),
if(Dimensionality()=1 and Region='LAM', Sum(Sales)+ Sum({<Region={'LAM'},Country={'Tyx'}>} total Sales),
if(Dimensionality()=1 and Region='EMEA', Sum(Sales)+ Sum({<Region={'EMEA'},Country={'Tyx'}>} total Sales),
if(Dimensionality()=1 and Region='ITS',Sum(Sales)+ Sum({<Region={'ITS'},Country={'Tyx'}>} total Sales),
if(Dimensionality()=1 and Region='JFK', Sum(Sales)+ Sum({<Region={'JFK'},Country={'Tyx'}>} total Sales),
if(Dimensionality()=1 and Region='BJC', Sum(Sales)+ Sum({<Region={'BJC'},Country={'Tyx'}>} total Sales),
if(Dimensionality()=1 and Region='KYC',Sum(Sales)+ Sum({<Region={'KYC'},Country={'Tyx'}>} total Sales),
Sum(Sales)))))))))
***********************************************************************************************************
How about this?
if(Dimensionality()=1 and Pick(Match(Region, 'NAM', 'APAC', 'LAM', 'EMEA', 'ITS', 'JFK', 'BJC', 'KYC'), Sum(Sales)+Sum({<Region={'NAM'},Country={'Tyx'}>} total Sales), Sum(Sales)+ Sum({<Region={'APAC'},Country={'Tyx'}>} total Sales), Sum(Sales)+ Sum({<Region={'LAM'},Country={'Tyx'}>} total Sales), Sum(Sales)+ Sum({<Region={'EMEA'},Country={'Tyx'}>} total Sales), Sum(Sales)+ Sum({<Region={'ITS'},Country={'Tyx'}>} total Sales), Sum(Sales)+ Sum({<Region={'JFK'},Country={'Tyx'}>} total Sales), Sum(Sales)+ Sum({<Region={'BJC'},Country={'Tyx'}>} total Sales), Sum(Sales)+ Sum({<Region={'KYC'},Country={'Tyx'}>} total Sales), Sum(Sales)))
Hi,
in the table where you use it, are you using Region as dimension?
In that case, you could try:
Sum(Sales)
+
If(Dimensionality()<>1 or Match(Region,'NAM','APAC','LAM','EMEA','ITS','JFK','BJC','KYC')=0, 0,
Sum({<Country={'Tyx'}>} total Sales))