topic Re: Calculated Dimension Help in QlikView

Calculated Dimension Help

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Calculated Dimension Help

Anil_Babu_Samineni — Fri, 17 Nov 2017 12:05:46 GMT

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

Re: Calculated Dimension Help

sunny_talwar — Fri, 17 Nov 2017 12:40:22 GMT

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)

Re: Calculated Dimension Help

Anonymous — Fri, 17 Nov 2017 15:12:39 GMT

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

Re: Calculated Dimension Help

Anonymous — Fri, 17 Nov 2017 15:54:44 GMT

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

Re: Calculated Dimension Help

sunny_talwar — Fri, 17 Nov 2017 16:01:30 GMT

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

Re: Calculated Dimension Help

sasiparupudi1 — Fri, 17 Nov 2017 16:07:48 GMT

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

Re: Calculated Dimension Help

Anonymous — Fri, 17 Nov 2017 16:16:40 GMT

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

Re: Calculated Dimension Help

sunny_talwar — Fri, 17 Nov 2017 16:18:23 GMT

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