Get values from the last 365 days based on other conditions

[jakobjensen]

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

[brijesh_fofadiy]

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]

Thanks!

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

[jakobjensen]

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

[Brett_Bleess]

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