New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

New Contributor II

Set Analysis > 0 in KPI

I'm trying to get a KPI to show a the last year/week where I have positive inventory. The underlying data set looks like the below. I'm trying to get a max yearweek with the set inventory greater than 0. Is this possible, what syntax would I use? Really appreciate any help.

 YearWeek Inventory 201801 1 201802 2 201803 3 201804 0
1 Solution

Accepted Solutions
Contributor II

Re: Set Analysis > 0 in KPI

Hi Mao,

If I am getting your question right then it may be the solution. Please check and let me know

=max({<Inventory={">0"}>}YearWeek)

Regards,

Neha

10 Replies
Contributor II

Re: Set Analysis > 0 in KPI

Hi Mao,

If I am getting your question right then it may be the solution. Please check and let me know

=max({<Inventory={">0"}>}YearWeek)

Regards,

Neha

Highlighted
Honored Contributor II

Re: Set Analysis > 0 in KPI

Hi,

Try like this?

Method 1: using set analysis

=sum( {\$<YearWeek={"\$(=Max(YearWeek)-1)"},Inventory={">0"}>}Inventory)

Esteemed Contributor

Re: Set Analysis > 0 in KPI

Hi devarasu07

in your expression i think if you remove the Inventory={">0"} part, it won't change nothing on the result no ?

Esteemed Contributor

Re: Set Analysis > 0 in KPI

Hi,

=sum({<YearWeek={"\$(=max({<Inventory={'>0'}>}YearWeek))"} >}  Inventory)

New Contributor II

Re: Set Analysis > 0 in KPI

Thank you both it's really helpful.

If I then also wanted to sum the inventory for all 3 weeks where the inventory is > than 0, how would I use set analysis to do that? Is that possible? (I have another column I want the values for)

Contributor II

Re: Set Analysis > 0 in KPI

Hi Eaphy,

How you are deciding which 3 weeks needs to be considered..

There would be other YearWeek also in your data which will have 0 values.

Regards,

neha

New Contributor II

Re: Set Analysis > 0 in KPI

Hi sorry I should have been more specific. I have a third column I want to sum (forecast), but I only want to consider the forecast where inventory is greater than 0. Is it possible to use set analysis to get the sum of the forecast for weeks where inventory is greater than 0?

 YearWeek Inventory Forecast 201801 1 2 201802 2 1 201803 3 3 201804 0 4 201805 0 5 201806 0 6
MVP

Re: Set Analysis > 0 in KPI

This should work

Sum({<YearWeek = {"=Sum(Inventory) > 0"}>}Forecast)

Contributor II

Re: Set Analysis > 0 in KPI

Hi Eaphy,

=sum({<Inventory={">0"}>}Forecast)