Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Coming your way, the Qlik Data Revolution Virtual Summit. October 27-29. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor III
Contributor III

Set Analysis

Hiya, 

 

I'm sure this has already been asked Many times, but I can't find a question that gives me the answer I need..

 

I'm trying to do a set analysis based on the year being this year and the month being the latest month. 

Currently this is my formula, (It doesn't work)

sum({$<Year={"2018"},Value,({$<Month={Max('SJL')}})})

But ideally I don't want to have to specify the Year and month as 2018  and January as I would have to change it every month. 

 

Does anyone know how to correct my formula to make this work?

 

Thanks, 

 

Megan

3 Replies
Highlighted
Partner
Partner

Hi,

This might help you,

Count( {<Year={"$(=Max(Year))"},Month={"$(=MaxString(Month))"}>} value)

I have used MaxString because my month are in String format, you can use Max(month) if your month field is in number format as the Max(month) returns the current month number.

Regards,

Prashant

Highlighted
Master III
Master III

You can do something like below using the current date

Year={"$(=Year(Today()))"}   Set Year to the Current Year

 

Month={"$(=Date(MonthStart(Today(),-1),'MMM'))"}   take the current date, read the Monthstart of the previous month (note -1)and format the result as month.

set analysis must always be with in the {<>} in your aggregation  ex: sum(Value)

 

sum(

{$<Year={"$(=Year(Today()))"}, Month={"$(=Date(MonthStart(Today(),-1),'MMM'))"}>} 

Value)

 

hth

Sas

Highlighted
Contributor
Contributor

sum({$<Year={"$(=Year(today()))"},Month={"$(=Month(today()))"}>}Value)