Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Leverage your QlikView investment to modernize BI – see how! Join Group
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor
Contributor

Calculation of YTD in script

Hi,

I have the following dataset:

 

DateClaim NumberProcessing StatusInjury Type
25-05-20201PSevere
25-05-20202NPMinor
01-06-20203PSevere
08-06-20204NPSevere
08-06-20205PMinor
15-06-20206NPSevere
15-06-20207NPSevere
15-06-20208NPSevere

 

I want to calculate YTD numbers for:

1. total processed (Count of claims with Processing status as P)

2. % processed (Count of claims with Processing status as P / all claims)*100

3. Total number of severe injuries

 I tried many methods like AsOfTable, Using RangeSum and Above combinations etc. All these works but the problem is I need to have a date filter in my dashboard. So when i select a particular date, the data is shown only for that date but i want it to be from the start of the year. 

 Any idea on how to implement ? Thanks in advance.

Labels (2)
1 Solution

Accepted Solutions
Highlighted
Specialist
Specialist

Re: Calculation of YTD in script

Try

Total YTD Processed

Count(DISTINCT {1<[Processing Status]={'P'}, Date=,Date={">=$(=(YearStart(Max(Date))))<=$(=Max(Date))"}>}[Claim Number])

 

%

(Count(DISTINCT {1<[Processing Status]={'P'}, Date=,Date={">=$(=(YearStart(Max(Date))))<=$(=Max(Date))"}>}[Claim Number])
/Count(DISTINCT TOTAL{1< Date=,Date={">=$(=(YearStart(Max(Date))))<=$(=Max(Date))"}>}[Claim Number])
)

 

Severe Injury Count

Count(DISTINCT {<[Injury Type]={'Severe'}>}[Claim Number])

 

Also. for further help refer 

YTD MTD QTD

View solution in original post

4 Replies
Highlighted
Specialist
Specialist

Re: Calculation of YTD in script

Try

Total YTD Processed

Count(DISTINCT {1<[Processing Status]={'P'}, Date=,Date={">=$(=(YearStart(Max(Date))))<=$(=Max(Date))"}>}[Claim Number])

 

%

(Count(DISTINCT {1<[Processing Status]={'P'}, Date=,Date={">=$(=(YearStart(Max(Date))))<=$(=Max(Date))"}>}[Claim Number])
/Count(DISTINCT TOTAL{1< Date=,Date={">=$(=(YearStart(Max(Date))))<=$(=Max(Date))"}>}[Claim Number])
)

 

Severe Injury Count

Count(DISTINCT {<[Injury Type]={'Severe'}>}[Claim Number])

 

Also. for further help refer 

YTD MTD QTD

View solution in original post

Highlighted

Re: Calculation of YTD in script

If you want selections to affect the calculations, you can't calculate it in the script.  You'll need to write an expression on the chart to calculate the YTD values.

Here are some good posts to get you started: 
https://community.qlik.com/t5/QlikView-Documents/Calculating-rolling-n-period-totals-averages-or-oth...
https://community.qlik.com/t5/QlikView-App-Development/How-to-calculate-YTD/m-p/1218324

Highlighted
Contributor
Contributor

Re: Calculation of YTD in script

Thanks for the reply @vamsee The given expression solves the purpose. But in the denominator for %processed expression, removing TOTAL keyword  gives the required result.

Also what is the reason that you have added 1 in all the expressions? The expression holds good even without the 1.

 

Anyways thanks much 🙂

Highlighted
Specialist
Specialist

Re: Calculation of YTD in script

As you mentioned in your post that selecting a date alters the chart and displays only for the date selected. "1" is an identifier.

$- Default identified, considers the current selections into account while calculating the chart

1- Does not consider any selections while calculating the chart

Refer

Identifiers in Qlik

set-analysis-qlikview

As your issue is resolved, please mark the correct answer and close the thread.

Thanks.