12 Replies Latest reply: Aug 7, 2017 9:41 AM by Steve Dark

# Calculation independent from selections

I have a dashboard with performance % for day selected but also want to include performance % for the whole week up to date selected.

Many thanks

Jess

• ###### Re: Calculation independent from selections

Hi Jessica,

The short answer is; you need to use Set Analysis.

The longer answer will take a bit more time...

This app may help point you in the right direction:

QlikView App: Set Analysis - Prior Period Comparison

Note that where that app uses the MonthStart function, there is also a WeekStart function that you will want to use.

Also, a note of warning on gauges (if you are using those to show percentage), make sure you don't have a dimension set for them or this will give spurious results (only the value for the first sorted entry in that dimension).

Good luck!

• ###### Re: Calculation independent from selections

What is your performance expression? May be something like this in set analysis

{<Day = , Week = p(Week)>}

• ###### Re: Calculation independent from selections

(count({\$< [ArrivalDate] = > }if(AEMinsInDept<=240,'')))/count({\$< [ArrivalDate] = > }INPATIENT_DATA_ID)

• ###### Re: Calculation independent from selections

Try this

Count({\$<[ArrivalDate], ArrivalWeek = p(ArrivalWeek), AEMinsInDept {'<=240'}>} INPATIENT_DATA_ID)/Count({\$<[ArrivalDate], ArrivalWeek = p(ArrivalWeek)>} INPATIENT_DATA_ID)

• ###### Re: Calculation independent from selections

This is the basic expression based on departure date

count(if(AEMinsInDept<=240,''))/count(INPATIENT_DATA_ID)

• ###### Re: Calculation independent from selections

Try this:

Count({\$<[ArrivalDate], ArrivalWeek = p(ArrivalWeek), AEMinsInDept {'<=240'}>} INPATIENT_DATA_ID)/Count({\$<[ArrivalDate], ArrivalWeek = p(ArrivalWeek)>} INPATIENT_DATA_ID)

• ###### Re: Calculation independent from selections

Unfortunately nothing is returned

• ###### Re: Calculation independent from selections

Do you have a field called ArrivalWeek or something similar in your dashboard?

• ###### Re: Calculation independent from selections

Only have arrival date and departure date fields

• ###### Re: Calculation independent from selections

I would solve that in the load script by doing this:

[Arrival Date],

Dual(WeekYear([Arrival Date]) & '-' & Week([Arrival Date]), WeekStart([Arrival Date])) as [Arrival Week],

Note that you will need the square brackets and space in the Set Analysis if you go with this.  Or you could just drop the space.

Steve

• ###### Re: Calculation independent from selections

Still showing nothing.

This is my preceding load script (selection is based on departuredate field):

*,

ArrDateTime as ArrivalTime2,

InitCompl as InitCompl2,

Date(Floor(DepartureDate)) as [DepDate],

Year(DepartureDate) as Year,

Month(DepartureDate) as Month,

Week(DepartureDate) as Week,

Day(DepartureDate) as Day,

Date(MonthStart(DepartureDate), 'MM-YYYY') as MonthYear,

Dual(WeekYear(DepartureDate)&'-'&Week(DepartureDate),WeekEnd(DepartureDate)) as [Arrival Week];

Expression:

Count({\$<[ArrivalDate], [Arrival Week] = p([Arrival Week]), AEMinsInDept {'<=240'}>} INPATIENT_DATA_ID)/Count({\$<[ArrivalDate], [Arrival Week] = p([Arrival Week])>} INPATIENT_DATA_ID)

• ###### Re: Calculation independent from selections

As you are selecting DepDate, you will need to exclude that selection in the set analysis:

Count({\$<[ArrivalDate]=, [Arrival Week] = p([Arrival Week]),DepDate=, AEMinsInDept={"<=240"}>} INPATIENT_DATA_ID)/Count({\$<[ArrivalDate]=, [Arrival Week] = p([Arrival Week]),DepDate=>} INPATIENT_DATA_ID)

Also, the expression was missing an equals sign after AEMinsInDept and was using the wrong quotes around the 240 minutes (though that that shouldn't have broken it).

Does your Arrival Date field have a space in it, by the way?

The best way to test Set Analysis functions is to add a bit in at a time and see what you get.

Try just this first:

Count({\$<AEMinsInDept {"<=240"}>} INPATIENT_DATA_ID)/Count({\$} INPATIENT_DATA_ID)

See if that works.  Then add some more:

Count({\$<[ArrivalDate]=,AEMinsInDept={"<=240"}>} INPATIENT_DATA_ID)/Count({\$<[ArrivalDate]=>} INPATIENT_DATA_ID)

Steve