Set analysis w/row Aggr help

jcampbell474 — Thu, 06 Jul 2017 00:03:45 GMT

In another thread, I was able to (successfully, I think) match two conditions using set analysis and aggr. Now, I've been at what I think is the final expression for a couple of days and can't figure it out. Reaching out for some help.

The prior expression: If two conditions within a variable date range match, then sum the count.

if(aggr(only({<datekey={"$(=Date(vdatekey))"}>}TelephoneNum),TelephoneNum) =

aggr(only({<datekey={"$(=Date(vdatekey - 1))"}>}TelephoneNum),TelephoneNum) or

aggr(only({<datekey={"$(=Date(vdatekey))"}>}TelephoneNum),cName) =

aggr(only({<datekey={"$(=Date(vdatekey - 1))"}>}TelephoneNum),cName),

sum({<datekey={"$(=Date(vdatekey - 1))"}>}Ct),0)

The expression I need help with: If the two conditions within a variable date range match AND the count = 0 then 1/1 else 0/1.

If(aggr(only({<datekey={"$(=Date(vdatekey))"}>}TelephoneNum),TelephoneNum) =

aggr(only({<datekey={">=$(vTmaxdate) <=$(vTmindate)"}>}TelephoneNum),TelephoneNum) or

aggr(only({<datekey={"$(=Date(vdatekey))"}>}TelephoneNum),cName) =

aggr(only({<datekey={">=$(vTmaxdate) <=$(vTmindate)"}>}TelephoneNum),cName) and

sum({<datekey={">=$(vTmaxdate) <=$(vTmindate)"}>}Ct)=0,1/1,0/1)

Note: I also created a model with a key to avoid the multiple matches and try to use intersections. Couldn't get it to work. If this is the more practical way, I would love to use it. Here's an expression I tried: (datakey is the concatenated key)

sum({<datakey=P(datakey),datekey={"$(=Date(vdatekey)-1)"}>} Ct)

Any and all help will be greatly appreciated.

Thanks,
Jason

Re: Set analysis w/row Aggr help

sunny_talwar — Thu, 06 Jul 2017 16:45:08 GMT

Hey Jason -

I am not entirely sure what you are after here... Would you be able to share a sample?

Re: Set analysis w/row Aggr help

jcampbell474 — Fri, 07 Jul 2017 11:47:32 GMT

Sunny, thank you for the reply. I ultimately used a composite key and was able to get what I needed.

if(aggr(only({<datekey={"$(=Date(vdatekey))"}>}datakey),datakey) =

aggr(only({<datekey={"<=$(vdatekey) >=$(vTmindate)"}>}datakey),datakey),

sum({<datekey={"<=$(vdatekey) >=$(vTmaxdate)"}>}Ct),0)

topic Re: Set analysis w/row Aggr help in QlikView

Set analysis w/row Aggr help

Re: Set analysis w/row Aggr help

Re: Set analysis w/row Aggr help