Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Krish2459_58
Creator II
Creator II

min and max data points

Hi, I have data like below. If you see the required output table below I need to show years with start and end data points. I can't supress zeros becuase I need to show Produts 5, 6.

Dimension: Year

Measure: Sum(Sales)

Prtoduct Sales year
1   2009
2 100 2010
3 300 2011
4 500 2012
5   2013
6   2014
7 600 2015
8   2016
9   2017
     
required output    
     
Prtoduct Sales year
2 100 2010
3 300 2011
4 500 2012
5   2013
6   2014
7 600 2015
Labels (2)
4 Replies
JandreKillianRIC
Partner Ambassador
Partner Ambassador

Hi @Krish2459_58 

I suppose you need to show years 1,8 and 9 as well? Im not seeing this in your output table. 

If you just add the Sum(Sales) as a measure and Prtoduct and Year as a dimension doesn't this give you the desired result? 

Regards Jandre

Mark the solution as accepted that solved your problem and if you found it useful, press the like button! Check out my YouTube Channel | Follow me on LinkedIn

Krish2459_58
Creator II
Creator II
Author

@JandreKillianRIC , I need to show all years between two points where data exixts. Just like required output table above.

Chanty4u
MVP
MVP

Try this 

If(Year = Min({1} Year) OR Year = Max({1} Year), Sum(Sales))

 

diegozecchini
Specialist
Specialist

An alternative approach is

If(Year = Min(Aggr(If(Sales>0, Year), Product))
OR
Year = Max(Aggr(If(Sales>0, Year), Product)),
Sales
)

This ensures that for each Product, only the first and last year with sales are displayed.