Group BY Year Aggr Help

[Vittore8]

good evening, I'm forming a set and getting
date column, can you please refactor my expression to get 2016

Count(Date(Aggr(if(Sum({<state={"successful"},main_category={"Food"}>} usd_pledged_real / backers)>=1000,[launched]),[launched]), 'YYYY')) 

this expression returns 7, but I need the year 2016

since 2016 only occurs 1 time, the most minimal

[Lauri]

I think you just need to add the Min function:

Count(Date(Min(Aggr(if(Sum({<state={successful},main_category={Food}>} usd_pledged_real / backers)>=1000,[launched]),[launched])), 'YYYY'))

Although if [launched] is a date field, I would create a year field from it in the load script and use that here. That way you are aggregating on the year, not individual days.

Also, take out the double quotes. Those are for formulas, not strings. (Use single quotes if your string contains spaces or special characters.) Qlik says that some day they will enforce strict rules...

[Vittore8]

I don't think this will solve my problem, because the result should = 2016

2016 -  is the least common
2016 - 1
2015 - 3
2917 - 3

[Or]

Your expression is wrapped up in a Count(), so it's never going to return the year, it's going to return the count of something...

I'm not really getting what you're trying to accomplish. Where exactly is this '2016' value supposed to show up? In a separate object? In a new column? Where are these year values getting pulled from in the first place?

I'd suggesting having a look at the FirstSortedValue() function, which is often the best way to get a dimension associated with the highest or lowest value of something.

[Lauri]

@Or is right. My result doesn't get you the year. I think you want to return the Year that has the smallest number of rows in the table that you showed, right?

[Vittore8]

@Lauri  Exacly!

[Vittore8]

@Or , 2 questions: How many successfully completed projects in the Food categories earned an average of more than a thousand dollars per person? In which year were the fewest such projects?

I got the answer to the first question, 7

in the screenshot with question, I just display the data for verification

[Vittore8]

here is qvf, can someone tell me how to get 2016

2 questions: How many successfully completed projects in the Food categories earned an average of more than a thousand dollars per person? In which year were the fewest such projects?

I got the answer to the first question, 7

[Lauri]

I took @Or's advice and summarized in the load editor. Unlock the script to change it:

[ks-projects-3]:
LOAD
[ID],
[Number of Records],
[backers],
[category],
[country],
[deadline],
[launched],
Year([launched]) as LaunchYr,
[main_category],
[name],
[state],
[usd_goal_real],
[usd_pledged_real],
if(state='successful' and main_category='Food' and (usd_pledged_real / backers)>=1000, 1,0) as Over1000
FROM [lib://AttachedFiles/ks-projects-3.csv]
(txt, unicode, embedded labels, delimiter is '\t', msq);

Summary:
Load LaunchYr,
sum(Over1000) as Over1000_Yr
Resident [ks-projects-3]
Group By LaunchYr;

Then the measure for your second question can be:

=FirstSortedValue(distinct LaunchYr, Over1000_Yr)