topic Re: Issue in Expression -Qlikview in QlikView

Issue in Expression -Qlikview

Thu, 14 Nov 2013 08:37:41 GMT

Hi,

1)Total Members : 100

Expression Used : = (SUM(IF(FLAG_MEM_PERSON_KEY=1 , FLAG_MEM_PERSON_KEY)))

2)Total Members who lost weight :

=(SUM(IF(FLAG_MEM_PERSON_KEY=1 and $(WeighLoss) < 0 , FLAG_MEM_PERSON_KEY)))

So WeighLoss is a variable that has weightloss value for each Userid.

Problem:

For Total Members who lost weight i should get a value as 1 (because UserID has a negative value which is less than 0.

But unfortunately this is not happening.

Can you please check the attached qvw .

Re: Issue in Expression -Qlikview

kaushiknsolanki — Thu, 14 Nov 2013 08:46:20 GMT

Hi,

Try this.

=sum(aggr(SUM(IF(FLAG_MEM_PERSON_KEY=1 and ($(WeighLoss))< 0 , FLAG_MEM_PERSON_KEY)),USER_ID))

Regards,

Kaushik Solanki

Re: Issue in Expression -Qlikview

DavidFoster1 — Thu, 14 Nov 2013 08:46:42 GMT

Lots of issues here:

1) Use set analysis instead of SUM(IF.... Sorry to be blunt but if you dont know set analysis then you need to stop and learn it.

2) The problem is you are summing a key field instead of counting them, or have a dedicated field that holds a 1 per person record that can then be used in SUMs.

Your expressions should be

COUNT({$<FLAG_MEM_PERSON_KEY={'1'}>} FLAG_MEM_PERSON_KEY)

COUNT({$<FLAG_MEM_PERSON_KEY={'1'}, WEIGHT_LOSS={"<0"}>} FLAG_MEM_PERSON_KEY)

on the assumption that weight loss is a field.

Re: Issue in Expression -Qlikview

bbi_mba_76 — Thu, 14 Nov 2013 08:51:34 GMT

Hi,

replacing the variable in the expression I found the following error: "nested aggregation not allowed"

Re: Issue in Expression -Qlikview

richard_chilvers — Thu, 14 Nov 2013 08:56:33 GMT

I have taken a quick look, but do not entirely understand how you are calculating the weight loss variable.

However, you may want to check the spelling of your variable shown in green above, which misses the 't' in weight. I don't think it will sort out your problem though

Re: Issue in Expression -Qlikview

Thu, 14 Nov 2013 09:01:26 GMT

Its not working

Re: Issue in Expression -Qlikview

kaushiknsolanki — Thu, 14 Nov 2013 09:02:59 GMT

HI,

What output you are expecting. I am getting 0.

Regards,

Kaushik Solanki

Re: Issue in Expression -Qlikview

Thu, 14 Nov 2013 09:06:36 GMT

Yea me too,

Because UserID 17 has lost weight(-3).

So here count should be 1.

Re: Issue in Expression -Qlikview

richard_chilvers — Thu, 14 Nov 2013 09:07:28 GMT

I agree with David. A small investment in time understanding Set Analysis now using this fairly simple example would be well spent, and help greatly in the future.

best regards

Re: Re: Issue in Expression -Qlikview

kaushiknsolanki — Thu, 14 Nov 2013 09:22:50 GMT

Hi,

Have a look at the attached application

Regards,

Kaushik Solanki

Re: Issue in Expression -Qlikview

Thu, 14 Nov 2013 09:43:06 GMT

Thanks a lot Kaushik

it worked

Re: Issue in Expression -Qlikview

Thu, 14 Nov 2013 09:45:28 GMT

Yea very true!!

Will surely follow your advice