Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

jerfwork
New Contributor

Expression COUNT from MinDate to Selected Date

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 DateCustomer
1/1/2015CustomerA
1/1/2015CustomerB
1/1/2016CustomerC
1/1/2016CustomerD
1/1/2017CustomerE
1/1/2018CustomerF

 

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

Year# Customer
20152
20164
20175
20186

 

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

Year# Customer
20186

 

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 ! 

Labels (3)
1 Solution

Accepted Solutions

Re: Expression COUNT from MinDate to Selected Date

Try this

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

Re: Expression COUNT from MinDate to Selected Date

Try this

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

Re: Expression COUNT from MinDate to Selected Date

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!