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

Announcements
Qlik Connect 2026! Turn data into bold moves, April 13 -15: Learn More!
cancel
Showing results for 
Search instead for 
Did you mean: 
jakobjensen
Contributor II
Contributor II

Get values from the last 365 days based on other conditions

Assume I have three fields; "values", "dates" and "ID".

I want to calculate, how many (distinct) IDs where the sum values with in the last year of a given date, is between 100 and 200

 

I have tried

 

count({< id = {"=sum(value)>100 and sum(value)<200"}, id={">(Date(dates.autoCalendar.Date-365))<=dates.autoCalendar.Date"}>} distinct id )

 

but it just returns "0" all the time.

 

Any hints to why this does not work and how to fix it? I am completely new to Qlik Sense

4 Replies
brijesh_fofadiy
Contributor III
Contributor III

Hi Jakob,

You can do as follow to get your result :

First Create 2 Variable to Store vMinDate(YearStartDate) and vMaxDate(YearEndDate).

It will store whole year means 365 days data.

and after that you can simple write below expression to get your desired output.

=Count({<Date={">=$(=Date(vMinDate))<=$(=Date(vMaxDate))"},Values={">100<200"}>}distinct ID)

Regards,

Brijesh

jakobjensen
Contributor II
Contributor II
Author

Thanks!

How do you do that in the expression editor? is YearEndDate a function? 

 

jakobjensen
Contributor II
Contributor II
Author

It has to be the sum of the values i.e sum(values)<200

Brett_Bleess
Former Employee
Former Employee

Best I can offer is the following Help link:

https://help.qlik.com/en-US/qlikview/April2020/Subsystems/Client/Content/QV_QlikView/Scripting/DateA...

Regards,
Brett

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.