topic Re: Calculation of YTD in script in QlikView

Calculation of YTD in script

vmurugananthan — Sat, 16 Nov 2024 00:34:04 GMT

Hi,

I have the following dataset:

Date	Claim Number	Processing Status	Injury Type
25-05-2020	1	P	Severe
25-05-2020	2	NP	Minor
01-06-2020	3	P	Severe
08-06-2020	4	NP	Severe
08-06-2020	5	P	Minor
15-06-2020	6	NP	Severe
15-06-2020	7	NP	Severe
15-06-2020	8	NP	Severe

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.

Re: Calculation of YTD in script

vamsee — Mon, 01 Jun 2020 19:59:23 GMT

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

Re: Calculation of YTD in script

Nicole-Smith — Mon, 01 Jun 2020 20:00:22 GMT

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-other/ta-p/1483033
https://community.qlik.com/t5/QlikView-App-Development/How-to-calculate-YTD/m-p/1218324

Re: Calculation of YTD in script

vmurugananthan — Wed, 03 Jun 2020 06:43:51 GMT

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 🙂

Re: Calculation of YTD in script

vamsee — Wed, 03 Jun 2020 15:18:26 GMT

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.