topic Re: Set analysis issue in QlikView

Set analysis issue

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

Hi,

I have this expression below which is giving a wrong result (No filters selected).

For funnelstamp2 =PQ Close and doc_signed dates selected in that range the value should be 0 for Eloview since there is no record where subscription is <7.

The expression is not taking the filters into consideration. Clicking on the filters makes the value 0.

What is wrong with the expression? Also, the strange thing is that it works when i am giving the subscription >7.

Re: Set analysis issue

girirajsinh — Sat, 14 Jan 2017 09:13:50 GMT

First, what result it shows when you give Subscription > 7 ?

You mentioned that there should be 0 for Subscription < 7. For that you may remove the 'if' condition and place "subscription={"<7"}" in the set analysis

Re: Set analysis issue

MK_QSL — Sat, 14 Jan 2017 09:38:55 GMT

Please provide sample data or sample application to test !

Re: Set analysis issue

vinieme12 — Sat, 14 Jan 2017 11:30:01 GMT

Replace ########## with the date format for doc_signed and quote_create_date respectively.

doc_signed={">=$(=Date#('10/01/2016','##########'))<$(=Date#('09/30/2017','##########'))"}

and

quote_create_date ={">=$(=Date#('07/02/2016','##########'))<$(=Date#('09/30/2017','##########'))"}

Re: Set analysis issue

sunny_talwar — Sat, 14 Jan 2017 12:40:43 GMT

I think the best way to resolve this would be to create your aggr table as a straight table.

Dimensions

[Quote number],

[Elo Project ID],

elo_owner

Team

Expression

Sum({<Doc_Signed = {'>=10/01/2016<09/30/2017'}, [Quote create date] = {'>=07/02/2016<09/30/2017'}, FunnelStamp2 = {'PQ Close'}>} subscription)

Now check if you see any values less than 7 or not? and then you can continue your troubleshoot from here to see if this expression needs to be changed or if you need to fix your aggr() dimensions.

Like Manish mentioned, it might be easier to help with a sample available to us.

Best,

Sunny

Re: Set analysis issue

Anonymous — Sat, 14 Jan 2017 14:15:44 GMT

Result from the straight table.

Re: Set analysis issue

sunny_talwar — Sat, 14 Jan 2017 14:18:29 GMT

What do you see with this expression now? in the same straight table:

If(Sum({<Doc_Signed = {'>=10/01/2016<09/30/2017'}, [Quote create date] = {'>=07/02/2016<09/30/2017'}, FunnelStamp2 = {'PQ Close'}>} subscription) < 7, (quote_quantity*0.005))

Re: Set analysis issue

Anonymous — Sat, 14 Jan 2017 14:30:45 GMT

Here is the result.

Re: Set analysis issue

sunny_talwar — Sat, 14 Jan 2017 14:34:58 GMT

I meant leave the first expression as it is and add this one. So, in short add these two expressions

1) Sum({<Doc_Signed = {'>=10/01/2016<09/30/2017'}, [Quote create date] = {'>=07/02/2016<09/30/2017'}, FunnelStamp2 = {'PQ Close'}>} subscription)

2) If(Sum({<Doc_Signed = {'>=10/01/2016<09/30/2017'}, [Quote create date] = {'>=07/02/2016<09/30/2017'}, FunnelStamp2 = {'PQ Close'}>} subscription) < 7, (quote_quantity*0.005))

My guess is that Sum({<Doc_Signed = {'>=10/01/2016<09/30/2017'}, [Quote create date] = {'>=07/02/2016<09/30/2017'}, FunnelStamp2 = {'PQ Close'}>} subscription) = 0 which is less than 7. If that is the case, may be your try like this

Sum(Aggr(

If(Sum({<Doc_Signed = {'>=10/01/2016<09/30/2017'}, [Quote create date] = {'>=07/02/2016<09/30/2017'}, FunnelStamp2 = {'PQ Close'}>} subscription) < 7

and

Sum({<Doc_Signed = {'>=10/01/2016<09/30/2017'}, [Quote create date] = {'>=07/02/2016<09/30/2017'}, FunnelStamp2 = {'PQ Close'}>} subscription) > 0

, (quote_quantity*0.005)), [Quote number], [Elo Project ID], elo_owner Team))

Re: Set analysis issue

Anonymous — Sat, 14 Jan 2017 14:50:27 GMT

Yay! That was the issue. Thank you!

Re: Set analysis issue

sunny_talwar — Sat, 14 Jan 2017 14:54:11 GMT

I am glad it helped. Breaking down the problem into small pieces is the best way to resolve issues such as these

Re: Set analysis issue

sunny_talwar — Sat, 14 Jan 2017 14:56:54 GMT

Now that I think about this, this might also work

Sum({<Doc_Signed = {'>=10/01/2016<09/30/2017'}, [Quote create date] = {'>=07/02/2016<09/30/2017'}, FunnelStamp2 = {'PQ Close'}>} Aggr(

If(Sum({<Doc_Signed = {'>=10/01/2016<09/30/2017'}, [Quote create date] = {'>=07/02/2016<09/30/2017'}, FunnelStamp2 = {'PQ Close'}>} subscription) < 7, (quote_quantity*0.005)), [Quote number], [Elo Project ID], elo_owner Team))

Where you use the same outer aggregation's set analysis as your inner aggregation's set analysis

Re: Set analysis issue

Anonymous — Thu, 26 Jan 2017 19:47:11 GMT

Hi Sunny,

Can you please take a look at this? Thanks!

Chart Design