Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Become an analytics expert with Qlik's new 15 week course: Applied Data Analytics using Qlik Sense. READ MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
eaphymaoweave
Contributor II
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.

YearWeekInventory
2018011
2018022
2018033
2018040
1 Solution

Accepted Solutions
neha_sri
Creator III
Creator III

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

View solution in original post

10 Replies
neha_sri
Creator III
Creator III

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

View solution in original post

devarasu07
Master II
Master II

Hi,

Try like this?

Method 1: using set analysis

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


Capture.JPG

YoussefBelloum
Champion
Champion

Hi devarasu07

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

YoussefBelloum
Champion
Champion

Hi,

I will add this:

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

eaphymaoweave
Contributor II
Contributor II
Author

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)

neha_sri
Creator III
Creator III

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

eaphymaoweave
Contributor II
Contributor II
Author

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?

 

YearWeekInventoryForecast
20180112
20180221
20180333
20180404
20180505
20180606
sunny_talwar

This should work

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

neha_sri
Creator III
Creator III

Hi Eaphy,

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

Please try

Regards,

Neha