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: 
snookersanders
Contributor II
Contributor II

How to use set analysis to compare filtered and unfiltered data

I am trying to list two numbers.  A. The amount of sales for SaleDate 2015 and B. The amount of credits with CreditDate in 2015.  I thought this would do it:  Sum( {1<CreditDate_RetailPeriod={SaleDate_RetailPeriod}>} CreditDet_NetSalesAmt), but that returns zero.  The idea is that I have a KPI which shows total sales and which the user may filter by SaleDate_RetailPeriod, but at the same time, I want to show total credits for the same retail period chosen by the user, but apply it to CreditDate instead of SaleDate and NOT show credits ONLY in the SaleDate period.  If I used two filters for the dates, that implies an AND, and I'm really looking for an OR on the two selections.

1 Solution

Accepted Solutions
felipedl
Partner - Specialist III
Partner - Specialist III

You could try using the following expression:
Sum( {<CreditDate_RetailPeriod=p(SaleDate_RetailPeriod)>} CreditDet_NetSalesAmt)

That would put in the CreditDate_RetailPeriod the possible values for SaleDate_RetailPeriod into the CrediDate.
So, if the user selects 2 or more dates in the SaleDate_RetailPeriod, it will put them into the CreditDate_RetailPeriod in the expression.

View solution in original post

5 Replies
felipedl
Partner - Specialist III
Partner - Specialist III

Can you share some data to the problem you're trying to solve?

Thanks.
snookersanders
Contributor II
Contributor II
Author

Hello felipedl,



Thanks for your reply



This is my first time using the Qlik Community and asking a question, so
I'll see if I can put together a small sample set of data to show the issue
and I'll post back when done.



Much appreciated. Snookersanders




felipedl
Partner - Specialist III
Partner - Specialist III

You could try using the following expression:
Sum( {<CreditDate_RetailPeriod=p(SaleDate_RetailPeriod)>} CreditDet_NetSalesAmt)

That would put in the CreditDate_RetailPeriod the possible values for SaleDate_RetailPeriod into the CrediDate.
So, if the user selects 2 or more dates in the SaleDate_RetailPeriod, it will put them into the CreditDate_RetailPeriod in the expression.
snookersanders
Contributor II
Contributor II
Author

Hey there Felipedl,



Much appreciated. I had to modify the expression just a bit to ignore
current selections.



One I added the '1' at the beginning of the set analysis expression, it
worked perfectly.



Here's the modified expression:



Sum( {1}
CreditDet_NetSalesAmt)



Much appreciated. I have been pulling my hair out over this one for days.



Later, have a good one, snookersanders




felipedl
Partner - Specialist III
Partner - Specialist III

Awsome, glad it helped even though you had to tweak it a little.

Felipe.