topic Re: Display value rather than sum out a value in QlikView

Display value rather than sum out a value

n1ef5ng1 — Thu, 29 Aug 2013 10:18:37 GMT

sum({<Master_Type_Indicator = {MOM},OPS_Direction = {'EW'}>}FEU) + (sum({<Master_Type_Indicator={'MOM'},OPS_Direction = {'EW'}>}FEU2) * 2))
/
(sum({<Master_Type_Indicator = {'MOM'},OPS_Direction = {'EW'}>}Capacity) * 2)
* 100

Hi, for this expression. I have three calculated field, FEU, FEU2 and Capacity. As example will be Bus, this particular but when going towards EW direction, it carry how many passenger (FEU) and how many passengers alighted (FEU2). Capacity is the bus capacity, how many passenger the bus are able to take.

THerefore, it would not be appropriate to use sum(condition)capacity as it will sum up to and fro of the capacity. say if the bus have 50 passenger capacity, the value will capture as 100 as i use SUM

is there any calculated code that I can use to display the capacity value rather than sum up the capacity value

Thanks appreciate a million

Re: Display value rather than sum out a value

Thu, 29 Aug 2013 11:33:37 GMT

Hi bennn,

replace sum() with only(). This sholud do the job.

HtH

Roland

Re: Display value rather than sum out a value

n1ef5ng1 — Fri, 30 Aug 2013 00:52:33 GMT

I tried but it gives me - as the output.

Re: Display value rather than sum out a value

Oleg_Troyansky — Fri, 30 Aug 2013 01:38:08 GMT

When function ONLY() returns a NULL (looks like a - ), it means that multiple values are available. Examine your data and decide what to do here:

1. If multiple values are not supposed to be available, then find and fix the problem.

2. If it's OK to have multiple values, then pick which value do you want - min, max, avg, or FirstSortedValue.

cheers,

Oleg

Re: Display value rather than sum out a value

n1ef5ng1 — Fri, 30 Aug 2013 02:00:43 GMT

For my scenario, I am trying to compile many 'buses' capacity (sum). however, those bus that have round trip will be recorded as Two * capacity while some bus that capture has one way trip will only count as one capacity if i use sum.

is there any ways I can sum up individual bus capacity instead

Re: Display value rather than sum out a value

Oleg_Troyansky — Fri, 30 Aug 2013 20:06:42 GMT

I'd probably have a separate field that always stores the actual capacity, disregarding of the route (round trip vs. one-way trip).

Re: Display value rather than sum out a value

CELAMBARASAN — Fri, 30 Aug 2013 20:13:10 GMT

If possible split capacity of the bus in to separate table.

use max or min

Max({<Master_Type_Indicator = {'MOM'},OPS_Direction = {'EW'}>}Capacity)