topic Calculated Dimension in QlikView

Calculated Dimension

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

Hi;

I'm trying to create a calculation dimension based on a condition. My formula isn't working:

=aggr(if(sum({<[In Violation] = {'1'}>}[Units In Violation],1,0), [Location Type]))

I'm trying to calculation the number of units in violation for each location type. Any help will be appreciated. Thanks.

Re: Calculated Dimension

trdandamudi — Sat, 13 Aug 2016 18:35:12 GMT

Is it possible to share a small sample..

Re: Calculated Dimension

sunny_talwar — Sat, 13 Aug 2016 18:48:38 GMT

How do you want your dimension to look like?

Re: Calculated Dimension

settu_periasamy — Sat, 13 Aug 2016 19:38:12 GMT

Not sure.. but seems to be condition is missing with if ..

Try like

=aggr(if(sum({<[In Violation] = {'1'}>}[Units In Violation])>0,1,0), [Location Type])

Re: Calculated Dimension

vinieme12 — Sun, 14 Aug 2016 06:46:44 GMT

You want to calculate the number of units in violation, then you should be adding an expression, not a calculated dimension.

Use this Expression:

SUM({<[In Violation] = {'1'}>}[Units In Violation])

Or this Dimension

AGGR(SUM({<[In Violation] = {'1'}>}[Units In Violation]),[Location Type])

Re: Calculated Dimension

pageomet — Sun, 14 Aug 2016 20:41:57 GMT

Thanks;

This was very helpful.

Re: Calculated Dimension

pageomet — Sun, 14 Aug 2016 21:11:42 GMT

Thanks Vineeth Pujari;

This formula worked.

Also can you help me with another formula? I'm trying to get the total sum in the calculated dimension. See table below:

Days Complete Count VIN

< 5 3

5 - 10 5

> 10 1

Total 9

I used the following expression and getting invalid dimension:

aggr(if([Days Complete] < 5 count([VINS]),

if([Days Complete] >= 5 and [Days Complete] <= 10 count([VINS]),

if([Days Complete] > 10 Count([VINS]))))),[Location])

Thanks,

Re: Calculated Dimension

vinieme12 — Mon, 15 Aug 2016 07:01:51 GMT

can you post sample data to demonstrate?

Your expression and dimension should be something as below

Dimension:

if([Days Complete] <5,'<5',

if([Days Complete] >= 5 and [Days Complete] <= 10,'5-10',

if([Days Complete] >10 ,'>10')))

Expression

SUM(VINS])

Re: Calculated Dimension

oknotsen — Mon, 15 Aug 2016 08:07:46 GMT

If your question is now answered, please flag the Correct Answer (via the big "Correct Answer" button near every post) and Helpful Answers (found under the Actions menu under every post).

If not, please make clear what part of this topic you still need help with .