topic Expression in QlikView

Expression

Mon, 10 Aug 2015 10:01:34 GMT

Hi all,

I have 2 columns. ID and Value like below.

ID Value

1245 10

1569 8

2145 15

2356 20

Sales = count of the "Value" that anything starts with number "1" in ID column.

Costs = count of the "Value" that anything starts with number "2" in ID column.

Can someone tell me how to write the expression.

Thanks

Re: Expression

avinashelite — Mon, 10 Aug 2015 10:07:11 GMT

try like this

sales : if(ID like '1*',count(Value))

cost: if(ID like '2*',count(Value))

Re: Expression

qlikmsg4u — Mon, 10 Aug 2015 10:09:15 GMT

=If(Left(ID,1)=1,Count(Value),Count(Value))

Sales : =If(Left(ID,1)=1,Count(Value))

Costs : =If(Left(ID,1)=2,Count(Value))

Re: Expression

maxgro — Mon, 10 Aug 2015 10:10:28 GMT

Sales = count of the "Value" that anything starts with number "1" in ID column.

Costs = count of the "Value" that anything starts with number "2" in ID column.

count({$ <ID={"1*"}>} Value)

Re: Expression

Kushal_Chawda — Mon, 10 Aug 2015 10:10:59 GMT

try

Sales: count({<ID ={"1*"}>}Value)

Costs: count({<ID ={"2*"}>}Value)

Re: Expression

MayilVahanan — Mon, 10 Aug 2015 10:13:11 GMT

Try like this

Value:

LOAD *, Pick(WildMatch(ID, '1*', '2*'), 1, 2,3) AS Type INLINE [

ID,Value

1245,10

1569, 8

2145,15

2356,20

];

Sales =Sum({<Type = {1}>}Value)

Cost =Sum({<Type = {2}>}Value)

Re: Expression

Mon, 10 Aug 2015 10:18:50 GMT

Thanks to both of you. Both expressions working fine.

Re: Expression

Mon, 10 Aug 2015 10:19:30 GMT

Thanks everyone.

Re: Expression

avinashelite — Mon, 10 Aug 2015 10:42:20 GMT

if you have got the answer please mark this thread as answer and close this thread ..so that it will be helpful for others

Re: Expression

qlikviewwizard — Tue, 11 Aug 2015 01:44:40 GMT

One question, different answers. Nice.