topic Re: Calculated Dimension in QlikView

Calculated Dimension

priyarane — Mon, 06 Apr 2015 09:36:55 GMT

Hi Experts,

I have straight table and first dimension is calculated dimension as below.

=if(HNAH_3460S = 'Y','HNAH_3460S' ,if(HUSI_3401S = 'Y','HUSI_3401S',if(HBUS_3607S = 'Y','HBUS_3607S',

if(HTCD_37130 = 'Y','HTCD_37130',if(HBIO_1201S = 'Y','HBIO_1201S',if(HSI_32900 = 'Y','HSI_32900',if(HMUS_3285S = 'Y','HSI_32900','')))))))

And filters are like below:

HBUS_3607S

HBIO_1201S

HMUS_3285S

HNAH_3460S

HSI_32900

HTCD_37130

HUSI_3401S

for each filter has Y or N values.

so my requirement as below.

If I select HUSI_3401S then in the dimension it should display 'HUSI_3401S' only but it displaying 'HNAH_3460S' eve if I select any other filters as Y then also it displaying 'HNAH_3460S'. is there any expression should write please suggest me on this.

PFA.

Re: Calculated Dimension

petter — Mon, 06 Apr 2015 10:00:40 GMT

According to what you show here this could be achieved much simpler by just:

- Create one List Box for the Legal Entity ID field.

- Then when you select in this List Box every row in your straight table will be filtered accordingly.

- The lookup you are using in the straight table should not be necessary as the association in your data

model (if you make it correctly) will give you the right values in this column.

But since I don't see your data model I can't judge whether this is a good solution for you.

Re: Calculated Dimension

sunny_talwar — Mon, 06 Apr 2015 10:07:37 GMT

May be making the if more complex will help:

=If(HNAH_3460S = 'Y' and GetSelectedCount(HNAH_3460S) = 1, 'HNAH_3460S', If(HUSI_3401S = 'Y' and GetSelectedCount(HUSI_3401S) = 1, 'HUSI_3401S', If(HBUS_3607S = 'Y' and GetSelectedCount(HBUS_3607S) = 1, 'HBUS_3607S', If(HTCD_37130 = 'Y' and GetSelectedCount(HTCD_37130) = 1, 'HTCD_37130', If(HBIO_1201S = 'Y' and GetSelectedCount(HBIO_1201S) = 1, 'HBIO_1201S', If(HSI_32900 = 'Y' and GetSelectedCount(HSI_32900) = 1, 'HSI_32900', If(HMUS_3285S = 'Y' and GetSelectedCount(HMUS_3258S) = 1, 'HSI_32900','')))))))

HTH

Best,

Sunny

Re: Calculated Dimension

priyarane — Mon, 06 Apr 2015 10:10:39 GMT

PFA for better clarity.

As suggested, If I take flags alone its showing perfect whether Y or N.

Re: Calculated Dimension

petter — Mon, 06 Apr 2015 10:14:27 GMT

My point is still valid. You should not need to complicate things with flags and a calculated dimension - which is very very expensive performance wise anyway. Try to make QlikView do the work for you in an uncomplicated manner and you will be able to deliver a solution to your customer more quickly and it will be much cheaper to extend and maintain too.

Re: Calculated Dimension

Anonymous — Mon, 06 Apr 2015 10:30:39 GMT

will solve the problem but will surely ruin the performance.

Re: Calculated Dimension

Anonymous — Mon, 06 Apr 2015 10:32:00 GMT

Try to push much of the calculation to script so that it will not take much of the time calculating in the front end.

Re: Calculated Dimension

sunny_talwar — Mon, 06 Apr 2015 10:41:15 GMT

Bhagirath‌I agree with you, but there are times you want the things to be dynamic and its just not possible to do everything in the back end of the application. In those times you need to use these functions and that's why they exist. If there is any chance of doing them in the back end, I would suggest Priyarane‌ to do it there.

HTH

Best,

Sunny

Re: Calculated Dimension

jagan — Mon, 06 Apr 2015 10:48:44 GMT

Hi,

Try this expression

=If(GetFieldSelections(HNAH_3460S) = 'Y', 'HNAH_3460S',

If(GetFieldSelections(HUSI_3401S) = 'Y', 'HUSI_3401S',

If(GetFieldSelections(HBUS_3607S) = 'Y', 'HBUS_3607S',

If(GetFieldSelections(HTCD_37130) = 'Y', 'HTCD_37130',

If(GetFieldSelections(HBIO_1201S) = 'Y', 'HBIO_1201S',

If(GetFieldSelections(HSI_32900) = 'Y', 'HSI_32900',

If(GetFieldSelections(HMUS_3258S) = 'Y', 'HSI_32900','')))))))

Hope this helps you.

Regards,

Jagan.

Re: Calculated Dimension

Anonymous — Mon, 06 Apr 2015 11:38:10 GMT

I agree with you that with the current info we have this is one of the feasible solutions

but if we would have received data model then may not be derivation of field (static) helped but we might have introduced some mapping or some minor things which can still help for better performance

Re: Calculated Dimension

priyarane — Mon, 06 Apr 2015 13:05:19 GMT

Its working Jagan.

Is it possible to write this at script level.

Re: Calculated Dimension

jagan — Tue, 07 Apr 2015 02:06:34 GMT

Hi Priya,

It is not possible to write this in script, because this expression depends on the Current Selections.

Hope this helps you.

Regards,

Jagan.