Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
rajarshi_guin
New Contributor III

Dimension Calculation

Hi,

In my Qlik Sense application, the data in present as below.

FrimCase NoStatusRecord Create Date
A10019/1/2017
A10029/10/2017
A10039/20/2017
A20019/1/2017
A20039/10/2017
A30019/10/2017
A30039/20/2017

The description if the status values are

 

StatusDescription
1Started
2In progress
3Complete

Now in my dashboard there are 2 filters. One is start date and end date. In the dashboard I need to show the Count of Closed Case by Firm for the period between the values selected in the filters start and end date.

The status of a case in the period between the values selected in the filters start and end date will be the max value of status within that period.


So, if the user selects Start Date=9/1/2017 and End Date=9/10/2017, then Count of Closed Case will be 1 as Case No 200 only has highest status as Complete within that period.


Can anyone please let me know the formula for this dimension.


Thanks,

Rajarshi Guin

3 Replies
prat1507
Valued Contributor

Re: Dimension Calculation

Maybe this

=count({<Status={'3'},[Record Create date]={'>=[Start Date]<=[End Date]'}>}distinct(Firm))

Regards
Pratyush

Partner
Partner

Re: Dimension Calculation

You can use a Straight table with:

Dim: Firm

Expression: count( {< Status = {'3'}, [Record Create date] = {">=$(vStartDate)<=$(vEndDate)"} >} DISTINCT [Case No] )

where vStartDate and vEndDate could be two input variables the user submitted.

Re: Dimension Calculation

Hi

Try like this

Dimension: Firm

Expression: Count( {< Status = {3}, [Record Create date] = {">=$(vStartDate)<=$(vEndDate)"} >} DISTINCT [Case No] )

Both vStartDate and vEndDate are the variables and its must be same date format as like "Record Create Date"