Expression COUNT from MinDate to Selected Date

jerfwork — Sat, 16 Nov 2024 03:46:45 GMT

Hi guys,

I'm trying to create an expression in my report in order to show the information from first day until selected date.

I have the raw data below:

Sign Up Date	Customer
1/1/2015	CustomerA
1/1/2015	CustomerB
1/1/2016	CustomerC
1/1/2016	CustomerD
1/1/2017	CustomerE
1/1/2018	CustomerF

I have a "Year" selection, if I never select anything, the result should add up accumulate as below:

Year	# Customer
2015	2
2016	4
2017	5
2018	6

If I selected 2018, my result should show as below (It will show the number from MinDate until Selected Date/Year):

Year	# Customer
2018	6

Currently I'm using below expression but it's not working. When I selected 2018, it show "1" instead of "6"

IF(GetSelectedCount(Year)=0,
COUNT({<Date = {"<=$(=Today())"}>} DISTINCT Customer),
COUNT({<Date = {"<=$(=Date(Max(Date)))">} DISTINCT Customer))

Thank you in advance !

Re: Expression COUNT from MinDate to Selected Date

sunny_talwar — Wed, 24 Apr 2019 11:37:24 GMT

Try this

RangeSum(Above(Count(DISTINCT {<Year>} Customer), 0, RowNo())) * Avg(1)

Re: Expression COUNT from MinDate to Selected Date

jerfwork — Wed, 24 Apr 2019 14:19:11 GMT

Hi Sunny, Thanks for your help! Your formula is working! but I think because of my data issue, I'm not getting the expected result. I will try to solve from data end. Thanks again for your help!

topic Re: Expression COUNT from MinDate to Selected Date in QlikView

Expression COUNT from MinDate to Selected Date

Re: Expression COUNT from MinDate to Selected Date

Re: Expression COUNT from MinDate to Selected Date