Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
jcampbell474
Creator III
Creator III

Set Analysis adding records to dimension

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?

fcrv2a.PNG

fcrv2b.PNG

1 Solution

Accepted Solutions
jcampbell474
Creator III
Creator III
Author

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)

View solution in original post

10 Replies
Anil_Babu_Samineni

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.

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
jcampbell474
Creator III
Creator III
Author

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

Anil_Babu_Samineni

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

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
jcampbell474
Creator III
Creator III
Author

It's a trigger in a button.

=GetFieldSelections(islandDate)

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

Anil_Babu_Samineni

May be give it a shot

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

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
jcampbell474
Creator III
Creator III
Author

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.

jcampbell474
Creator III
Creator III
Author

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

jcampbell474
Creator III
Creator III
Author

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)

Anil_Babu_Samineni

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

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)