Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

jorn_vango
New Contributor III

Data Handling -- Calculation condition

I have a table that shows county, sum(sales), sum(sales 6 months ago), sum(difference in sales).

The table shows the county and how the all-time sales through today differ from the all-time sales through (today - 6 months).

The goal is to show the biggest gainers and losers of the past 6 months.

I can use "fixed number -> top 5" to show the 5 biggest gainers.

How do I show the counties that lost the most, starting from the counties that were the biggest 6 months ago?  I tried using "top 5" after filtering the table to show counties with no difference in sales (of which there are plenty), but I can't get the "Data handling -> calculation condition" to work.

Thanks!

5 Replies
Digvijay_Singh
Honored Contributor III

Re: Data Handling -- Calculation condition

Are you sure you want to use Calculation condition here, this property is used to decide whether to display/calculate  chart or not, based on some selections or filter value.

Can you share sample data and expected output to understand the problem you are facing?

jorn_vango
New Contributor III

Re: Data Handling -- Calculation condition

jorn_vango
New Contributor III

Re: Data Handling -- Calculation condition

Perhaps a different way to explain is that I have a table with county, total all-time sales amount as it was 6 months ago, total all-time sales amount as it is today, and the difference.

What I want to do is show the top 5 counties that ...

1) had the highest sales amount 6 months ago

and

2) had no sales since then (so the difference is 0)

So, I have my table ordered by the past sales (highest first).  If I can now filter the entire table to only show counties where the sales didn't change, that would give the result that I'm looking for.

I tried to use Calculation Condition but the below doesn't show data in my table, even though there are many counties where the sales at 6 months ago are the same as they are today (=no extra sales happened these past 6 months).

If( Sum( {<[Geo_County]>} LoanCount_Past)   =   Sum( {<Geo_County]>} LoanCount_Today), 1, 0)

Digvijay_Singh
Honored Contributor III

Re: Data Handling -- Calculation condition

I see that your test data has sales values against customer IDs but if I read the details you have provided you want to use county as your dimension. You got just two counties in the sample data, so when we use aggregation functions like sum() and Use county as a dimension, in reality you don't have any 0 records for your sales values for these two counties.( See the third table), In fact past sales won't match with current sales when numbers are aggregated for counties, I can see they do match for some customer Ids but not when you use county as your dimension.

I think first part of your problem is handled in table 1. I am still trying to understand if your sample data is enough to manage part 2.

jorn_vango
New Contributor III

Re: Data Handling -- Calculation condition

I did a workaround by changing what is shown.  I added the "qsVariable" extension and allow the user to set the minimum value of the past loan count.  See https://community.qlik.com/message/1437289?et=watches.email.thread#1437289