topic Set Analysis adding records to dimension in QlikView

Set Analysis adding records to dimension

jcampbell474 — Mon, 03 Jul 2017 17:29:22 GMT

I'm working on a app that uses set analysis to tell me how many times a telephone number called on each previous selected day. I thought that it would only look for records in the dimension, but if a telephone called on the day before, it adds that record in the dimension. I need to get the set analysis to look back to the base day (dimension), but now I'm thinking aggr might be needed.

Here's my expression. I have 30 of them. -1, -2, -3, and the further I go back, the more telephone numbers are added.

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

I've searched and can't find a way to implement it. Can anyone tell me what I'm doing wrong or if I'm taking the wrong approach?

Re: Set Analysis adding records to dimension

Anil_Babu_Samineni — Mon, 03 Jul 2017 17:35:41 GMT

I am not looking your screenshot currently due to phone app

Here, you can show the variable how you are declare and first part can you move to single quote from double quotes in Set analysis.

Re: Set Analysis adding records to dimension

jcampbell474 — Mon, 03 Jul 2017 17:39:59 GMT

The variable is a date. It works - get's the right date. I changed the double quotes to single and it didn't change.

Re: Set Analysis adding records to dimension

Anil_Babu_Samineni — Mon, 03 Jul 2017 17:41:43 GMT

Will you able to share the variable expression? I am sorry, if I miss read the question

Re: Set Analysis adding records to dimension

jcampbell474 — Mon, 03 Jul 2017 17:44:45 GMT

It's a trigger in a button.

=GetFieldSelections(islandDate)

The user selects a date (that is not tied to the data), which populates vdatekey.

Re: Set Analysis adding records to dimension

Anil_Babu_Samineni — Mon, 03 Jul 2017 17:48:02 GMT

May be give it a shot

sum({<islandDate={"$(=Date(vdatekey - 1))"}>}Ct)

Re: Set Analysis adding records to dimension

jcampbell474 — Mon, 03 Jul 2017 17:50:15 GMT

The field islanddate isn't tied to the data. It can't be used. It is just an orphaned table. Datekey is tied to the data. It's intersecting on telephone numbers, and I need it to take the base date into consideration, too.

Re: Set Analysis adding records to dimension

jcampbell474 — Mon, 03 Jul 2017 19:31:05 GMT

Here's a few expressions that I've tried. I need the set to filter to only the telephone numbers on datekey or vdatekey (they are both the same date). Telephone number is the dimension.

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

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

//if(sum({<datekey={'$(vdatekey)'}>}Ct)=0,'',sum(aggr(sum({<datekey={"$(=Date(vdatekey - 1))"}>}Ct),TelephoneNum)))

//if(sum(aggr(sum({<datekey={"$(=Date(vdatekey))"}>}Ct),TelephoneNum))=0,'',sum(aggr(sum({<datekey={"$(=Date(vdatekey - 1))"}>}Ct),TelephoneNum)))

Re: Set Analysis adding records to dimension

jcampbell474 — Mon, 03 Jul 2017 20:13:09 GMT

Figured it out. Fyi, here is the expression:

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

Re: Set Analysis adding records to dimension

Anil_Babu_Samineni — Tue, 04 Jul 2017 00:25:24 GMT

Good and thanks for update. You can flag your response as correct answer

Re: Set Analysis adding records to dimension

jcampbell474 — Wed, 05 Jul 2017 11:37:58 GMT

Thanks. Tweaked it a little more. Here's the final result:

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)