topic Re: Set analysis and min() in QlikView

Set analysis and min()

Sun, 25 Oct 2015 09:12:20 GMT

Hello,

I'm trying to calculate resellers' sales growth from the first year they started selling so the first year where sum(#DealAmount)>0.

The expression I'm using the identify that year is the one below, but it doesn't seem to work consistently and doesn't calculate the individual min(Year) at dimension level.

min ({<Year ={"=sum(#DealAmount) >0"}>} Year)

For the same reason, my expression for calculating sales in the first year doesn't work either:

sum({ < Year= {$(=Min({ <Year ={"=sum(#DealAmount) >0"}>}Year))}>} #DealAmount)

I'm probably missing something silly... Any idea of what that might be? How would you calculate this?

Thanks,

Linda

Re: Set analysis and min()

Anonymous — Sun, 25 Oct 2015 10:25:41 GMT

Hi Linda,

May be try:

min ({<[#DealAmount] -= {'0'}>} Year)

sum({ < Year= {"$(=Min(Year)"},[#DealAmount] -= {'0'}>} [#DealAmount])

min ({<[#DealAmount] > 0 >} Year)

sum({ < Year= {"$(=Min(Year)"},[#DealAmount] > 0>} [#DealAmount])

Re: Set analysis and min()

Sun, 25 Oct 2015 10:53:46 GMT

Hi Neetha,

Part of your second suggestion worked with a small modification - see below:

min ({<[#DealAmount] = {">0"} >} Year)

The bad news is that although the above returns the correct year in a single expression, when I place it in the sales calculation expression it just returns the same value for all dimension entries, that value being the min(Year) where any of the resellers made sales.

sum({ < Year= {$(=min ({<[#DealAmount] = {">0"} >} Year))}>} #DealAmount)

To me it really doesn't make sense because both expressions are in the same straight table and resellers are the dimension...

Thanks,

Linda

Re: Set analysis and min()

Anonymous — Sun, 25 Oct 2015 11:17:24 GMT

may be:

sum({ < Year= {"$(=Min({ <Year ={"=sum(#DealAmount) >0"}>}Year)")}>} #DealAmount)

Re: Set analysis and min()

Sun, 25 Oct 2015 11:21:53 GMT

Nope. It returns the same results as my original one...

Re: Set analysis and min()

Anonymous — Sun, 25 Oct 2015 15:24:16 GMT

hi linda,

may be try:

=sum({< Year= {"$(=min ({<[#DealAmount] = {">0"}>} Year))"}>} [#DealAmount])

=sum({< Year= {"$(=min ({<#DealAmount = {">0"}>} Year))"}>} #DealAmount)

Re: Set analysis and min()

MK_QSL — Sun, 25 Oct 2015 17:55:19 GMT

Set Expressions are evaluated before the aggregation made so you can't expect different Set Expression for each row of any straight table.

Better if you can calculate the min Year in script itself.

Re: Set analysis and min()

Oleg_Troyansky — Sun, 25 Oct 2015 21:34:17 GMT

Hi Linda,

the first question to ask yourself when formulating advanced Set Analysis filters is - what field should I apply the condition to? Is it Amount? Year? Reseller? Based on your initial question, it looks like Reseller needs to be the one:

"I'm trying to calculate resellers' sales growth from the first year they started selling so the first year where sum(#DealAmount)>0."

With this in mind, your first option would return the sum of the Deal Amount for the first year in your dataset (across all resellers:

sum({ < Year= {$(=Min({ <Year ={"=sum(#DealAmount) >0"}>}Year))}>} #DealAmount)

If Reseller is one of your chart dimensions, then Set Analysis cannot be used here, at least on directly. As someone already mentioned in this thread, Set Analysis condition is only calculated once per chart, not once per chart line. If this is the case, you may have to calculate a conditional flag in your data that could make your condition more "static", - in other words, dependent on your chart dimensions, but indirectly.

For example, if you could mark all the sales in the first year for each reseller with a conditional flag _FirstYear_Flag (you'd do it in your script), then the calculation for the "First Year Sales" would look rather simple:

sum({ < _FirstYear_Flag = {1} >} #DealAmount)

Check out my new book QlikView Your Business. In the book, I'm describing in detail how to use advanced Set Analysis conditions for a variety of analytical needs. I also teach an extensive Set Analysis & AGGR session at the Masters Summit for QlikView - check out our agenda!

Cheers,

Oleg Troyansky

Check out my new book QlikView Your Business: An expert guide to Business Discovery with QlikView and Qlik Sense

Re: Set analysis and min()

Mon, 26 Oct 2015 11:24:18 GMT

Hi Oleg,

Thanks for your reply. Just so that you know I am deep into your book these days, which I got directly from you at the Masters Summit in Copenhagen I'm finding it really helpful and am getting many exciting ideas!

I completely understand what you're saying and was thinking of using flags first but because I didn't like some of the implications I tried this other way. What I really don't get is why min ({<[#DealAmount] = {">0"} >} Year) returns the correct year at Reseller level whilst the sales calcultation, which includes this same expression, doesn't... I don't see any logic in it and that's why I'm struggling!

Could I then refer to the result of min ({<[#DealAmount] = {">0"} >} Year) in the sales calculation as a workaround? Not sure I can refer to straight table columns in set analysis though...

Thanks,

Linda

Re: Set analysis and min()

Oleg_Troyansky — Tue, 27 Oct 2015 13:55:08 GMT

Hi Linda,

I had a feeling that I remember your name from the Summit 🙂

Yes, I understand how these things may look confusing. It makes sense if you try to visualize what's going on in each case:

min ({<[#DealAmount] = {">0"} >} Year) - works, because the SA condition filters all Years in which there were any Sales, irrespective of Reseller. Then, the min() function calculates the minimum Year, aggregated at the Reseller level. For each reseller, its individual min Year is calculated.

On the other hand, the following doesn't work:

sum({ < Year= {$(=Min({ <Year ={"=sum(#DealAmount) >0"}>}Year))}>} #DealAmount)

The SA condition calculates the minimum of Year with sum(Sales) >0 - this is a single Year, globally, irrespective of any Resellers. Then, this information cannot be presented by Reseller because the Year was already set to the global min of Sales Year.

To your question -0 no, you can't refer to other columns in Set Analysis. If you can't work with flags for some reason, you might have to revert to the IF() conditions, if the size of the database allows that...

enjoy the book!

Oleg Troyansky