Set analysis issue

[Anonymous]

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.

[girirajsinh]

Hi

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

[MK_QSL]

Please provide sample data or sample application to test !

[vinieme12]

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','##########'))"}

[sunny_talwar]

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

[Anonymous]

Result from the straight table.

[sunny_talwar]

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))

[Anonymous]

Here is the result.

[sunny_talwar · Accepted solution]

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))

[Anonymous]

Yay! That was the issue. Thank you!