topic Re: How to use set analysis to compare filtered and unfiltered data in App Development

How to use set analysis to compare filtered and unfiltered data

snookersanders — Sat, 16 Nov 2024 20:39:34 GMT

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.

Re: How to use set analysis to compare filtered and unfiltered data

felipedl — Mon, 17 Jun 2019 13:54:13 GMT

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

Thanks.

Re: How to use set analysis to compare filtered and unfiltered data

snookersanders — Mon, 17 Jun 2019 17:51:00 GMT

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

Re: How to use set analysis to compare filtered and unfiltered data

felipedl — Mon, 17 Jun 2019 19:15:39 GMT

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.

Re: How to use set analysis to compare filtered and unfiltered data

snookersanders — Mon, 17 Jun 2019 19:48:00 GMT

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

Re: How to use set analysis to compare filtered and unfiltered data

felipedl — Mon, 17 Jun 2019 20:53:07 GMT

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

Felipe.