Calculated Dimension Help

[Anonymous]

Hi guys,

I have the following calculated dimension(you guys might have a better way of writing this by the way - open to suggestions!):

=IF(NOT([BU Category Code 19 - Code] = 'IBS'), [BU Category Code 19 - Code],)

Is there a way I can edit the above so that if I click on one of the dimensions results, it changes all other tables except this one? I want this to stay static. I have tried placing a "{1}" into a couple of places within the dimension but I keep getting errors.

Many thanks,

Gareth

[Anil_Babu_Samineni]

But Calculated Dimension returns only one value right? May be use expression {1} ??

[sunny_talwar]

Anil is right, to make it static, you should add {1} to your expression. But I would also suggest using set analysis instead of calculated dimension here

Dimension

[BU Category Code 19 - Code]

Expression (Assumption that your expression is Sum(Measure))

Sum({1<[BU Category Code 19 - Code] = e({<[BU Category Code 19 - Code] = {'IBS'}>})>}Measure)

[Anonymous]

Sorry, the Calculated Dimension I provided returns more than one value. Upon clicking one of these values I want the straight table featuring this calculated dimension to stay the same but all other objects can change as necessary.

Thanks,

Gareth

[Anonymous]

Hi Sunny,

My expression is as follows, so how would I amend it to include your suggestion?

( Count({<[Year]={$(=Year(Today()))}, [FTMR_P2P_KPI]={"Success"},[Purchase Order Document Type - Code]-={''},[Month]={'Feb'}>} DISTINCT [Invoice Number]))

/

( Count({<[Year]={$(=Year(Today()))}, [FTMR_P2P_KPI]={"Success","Failure"},[Purchase Order Document Type - Code]-={''},[Month]={'Feb'}>} DISTINCT [Invoice Number]))

Many thanks,

Gareth

[sunny_talwar]

Like this

(Count({1<[Year] = {$(=Year(Today()))}, [FTMR_P2P_KPI] = {"Success"}, [Purchase Order Document Type - Code] =- {''}, [Month] = {'Feb'}, [BU Category Code 19 - Code] = e({<[BU Category Code 19 - Code] = {'IBS'}>})>} DISTINCT [Invoice Number]))

/

(Count({1<[Year] = {$(=Year(Today()))}, [FTMR_P2P_KPI] = {"Success","Failure"}, [Purchase Order Document Type - Code] =- {''}, [Month] = {'Feb'}, [BU Category Code 19 - Code] = e({<[BU Category Code 19 - Code] = {'IBS'}>})>} DISTINCT [Invoice Number]))

[sasiparupudi1]

Dim-> [BU Category Code 19 - Code]

( Count({<[BU Category Code 19 - Code]-={'IBS'},[Year]={$(=Year(Today()))}, [FTMR_P2P_KPI]={"Success"},[Purchase Order Document Type - Code]-={''},[Month]={'Feb'}>} DISTINCT [Invoice Number]))

/

( Count({<BU Category Code 19 - Code]-={'IBS'},[Year]={$(=Year(Today()))}, [FTMR_P2P_KPI]={"Success","Failure"},[Purchase Order Document Type - Code]-={''},[Month]={'Feb'}>} DISTINCT [Invoice Number]))

[Anonymous]

Thanks all.

The expressions work to omit IBS from the list BU Category 19 - Code pulls back, but they don't hold the list in place if I click on one of the values returned by BU Category 19 - Code.

Any ideas? Again, I'm pretty sure I need a {1} but where to put this into the expressions above I have no idea.

Thanks,

Gareth

[sunny_talwar]

Try this

(Count({1<[Year] = {$(=Year(Today()))}, [FTMR_P2P_KPI] = {"Success"}, [Purchase Order Document Type - Code] =- {''}, [Month] = {'Feb'}, [BU Category Code 19 - Code] = e({1<[BU Category Code 19 - Code] = {'IBS'}>})>} DISTINCT [Invoice Number]))

/

(Count({1<[Year] = {$(=Year(Today()))}, [FTMR_P2P_KPI] = {"Success","Failure"}, [Purchase Order Document Type - Code] =- {''}, [Month] = {'Feb'}, [BU Category Code 19 - Code] = e({1<[BU Category Code 19 - Code] = {'IBS'}>})>} DISTINCT [Invoice Number]))