Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Highlighted
vidyasagar159
Contributor II

In which year which fruit has crossed more than $1000 sales

Hi All,

I need some help. From the below sample data. I would like to identify only the records of the fruits that crossed the $1000 sales for the first time.

Example: Grapes has crossed $1000 first time in 2015. I just want to show that one record I do not want to see all the years of the fruits which crossed greater than $1000.

FruitsYearsales
Apples2015$500
Apples2016$700
Apples2017$1,100
Grapes2015$1,200
Grapes2016$600
Grapes2017$1,100
Mangoes2015$1,000
Mangoes2016$1,300
Mangoes2017$1,200

 

Thanks,

-Vidya

3 Solutions

Accepted Solutions

Re: In which year which fruit has crossed more than $1000 sales

Check this out

image.png

Dimension

Fruits

Expressions

Min({<sales = {'>=1000'}>}Year)
FirstSortedValue({<sales = {'>=1000'}>} sales, Year)

Re: In which year which fruit has crossed more than $1000 sales

Try this script

Table:
LOAD * INLINE [
    Fruits, Year, sales
    Apples, 2015, 500
    Apples, 2016, 700
    Apples, 2017, 1100
    Grapes, 2015, 1200
    Grapes, 2016, 600
    Grapes, 2017, 1100
    Mangoes, 2015, 1000
    Mangoes, 2016, 1300
    Mangoes, 2017, 1200
];

Right Join (Table)
LOAD Fruits,
	 Min(Year) as Year
Resident Table
Where sales >= 1000
Group By Fruits;
vidyasagar159
Contributor II

Re: In which year which fruit has crossed more than $1000 sales

Thanks for all your help today.
8 Replies

Re: In which year which fruit has crossed more than $1000 sales

So, you want to see 1 row per each fruit? 2015 for Grapes, 2017 for Apples, and 2015 for Mangoes?

vidyasagar159
Contributor II

Re: In which year which fruit has crossed more than $1000 sales

Thats correct.

Re: In which year which fruit has crossed more than $1000 sales

Check this out

image.png

Dimension

Fruits

Expressions

Min({<sales = {'>=1000'}>}Year)
FirstSortedValue({<sales = {'>=1000'}>} sales, Year)
vidyasagar159
Contributor II

Re: In which year which fruit has crossed more than $1000 sales

Awesome this is exactly what I am looking for. By any chance can we achieve the same in the load script editor? 

Re: In which year which fruit has crossed more than $1000 sales

So, you mean that bring in only 3 rows and drop all other rows?

vidyasagar159
Contributor II

Re: In which year which fruit has crossed more than $1000 sales

Yes, That's correct.

Re: In which year which fruit has crossed more than $1000 sales

Try this script

Table:
LOAD * INLINE [
    Fruits, Year, sales
    Apples, 2015, 500
    Apples, 2016, 700
    Apples, 2017, 1100
    Grapes, 2015, 1200
    Grapes, 2016, 600
    Grapes, 2017, 1100
    Mangoes, 2015, 1000
    Mangoes, 2016, 1300
    Mangoes, 2017, 1200
];

Right Join (Table)
LOAD Fruits,
	 Min(Year) as Year
Resident Table
Where sales >= 1000
Group By Fruits;
vidyasagar159
Contributor II

Re: In which year which fruit has crossed more than $1000 sales

Thanks for all your help today.