topic Set Expression - Advanced in App Development

Set Expression - Advanced

Ron1 — Thu, 05 Sep 2019 14:32:03 GMT

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)))))))))

***********************************************************************************************************

Re: Set Expression - Advanced

Anil_Babu_Samineni — Thu, 05 Sep 2019 14:44:07 GMT

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)))

Re: Set Expression - Advanced

StarinieriG — Thu, 05 Sep 2019 15:07:39 GMT

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))