topic Re: Applying a date in KPI calculation in QlikView

Applying a date in KPI calculation

shyamcharan — Wed, 06 Jul 2016 00:47:59 GMT

Hi Experts,

I have a scenario where there are two Dates. One is responded date and the Other Arrival date.

Arrival Date could be today but the responded dates could be Today and Yesterday.

For example:

Arrival Date = 06-07-2016 12:05:00 AM

Responded Date = 05-07-2016 11:55:00 PM

Arrival Date = 06-07-2016 12:55:00 AM

Responded Date = 06-07-2016 12:15:00 AM

In both the above scenarios I have one Arrival Date, i.e 06-07-2016.

However, I have only Responded Date filter selection for the user to avoid confusion. But I want the KPI to be calculated using the value of the Responded date selection.

For example:

If user selects 05-07-2016 as the responded date then my KPI(TOC) should use

Count({<Arrival Date = 05-07-2016>}Measure).

Please find the attached Document for more details.

Thanks in advance.

Shyam.

Re: Applying a date in KPI calculation

shyamcharan — Wed, 06 Jul 2016 02:21:28 GMT

Hi Again,

I have tried using Triggers in the document and it seems something is working.

However, I am wondering if there are any unknown flaws in this approach.

Before this fix:

When I select 28-06-2016 in Responded Date the value TOC = 26.48

With the fix,(Using triggers):

When I select 28-06-2016 in Responded Date the Arrived Date gets selected and the value TOC = 25.80

Please find the attached fixed application and suggest.

Thanks in advance.

Shyam.

Re: Applying a date in KPI calculation

shyamcharan — Wed, 06 Jul 2016 03:28:34 GMT

Hi Again,

I am facing an issue with my fix in my earlier application.

When I make a selection on other filters like RT_PK_ID i am losing the filter on Arrived Date.

I have applied a trigger on Actual Date Field (Responded Date) OnChange to clear the Arrived Date.

Since there is no additional filter to select back the Arrived Date, now I am stuck with my approach.

Any better ideas?

Please let me know if any of my posts does not make any sense

Thanks in advance.

Shyam.

Re: Applying a date in KPI calculation

maksim_senin — Wed, 06 Jul 2016 04:06:10 GMT

Hi,

It seems that alternative states is your choice. Using alt. states you can have several filters for one field and apply filters via set analysis.

Best regards,

Maxim

Re: Applying a date in KPI calculation

shyamcharan — Wed, 06 Jul 2016 04:26:23 GMT

Hi Maxim,

Thanks for your response.

Not sure if I need alternate States.

What all I want is to apply the Respond Date field selection value to the Arrived Date in calculation.

Something like this:

Sum({<DIMDATE_ARRIVEDESTINATION.ArriveDestination_Date="$(vResponseDate)"},Actual_Date>}MeasureField)

Where vResponseDate = =Responded_Date

But I do not get correct results.

Thanks in advance.

Re: Applying a date in KPI calculation

Wed, 06 Jul 2016 06:12:54 GMT

Creating 2 variables for the dates (Respond and Arrival) and linking both to a master calendar might also work. These 2 variables can be used with set analysis in your expressions to get the desired results.