topic Re: Calculation independent from selections in Connectivity & Data Prep

Calculation independent from selections

Anonymous — Fri, 02 Apr 2021 08:01:05 GMT

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

stevedark — Mon, 07 Aug 2017 10:40:50 GMT

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

sunny_talwar — Mon, 07 Aug 2017 12:02:15 GMT

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

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

Re: Calculation independent from selections

Anonymous — Mon, 07 Aug 2017 12:05:01 GMT

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

Re: Calculation independent from selections

sunny_talwar — Mon, 07 Aug 2017 12:08:46 GMT

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

Anonymous — Mon, 07 Aug 2017 12:14:55 GMT

This is the basic expression based on departure date

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

Re: Calculation independent from selections

sunny_talwar — Mon, 07 Aug 2017 12:15:44 GMT

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

Anonymous — Mon, 07 Aug 2017 12:18:44 GMT

Unfortunately nothing is returned

Re: Calculation independent from selections

sunny_talwar — Mon, 07 Aug 2017 12:23:42 GMT

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

Re: Calculation independent from selections

Anonymous — Mon, 07 Aug 2017 12:59:12 GMT

Only have arrival date and departure date fields

Re: Calculation independent from selections

stevedark — Mon, 07 Aug 2017 13:18:18 GMT

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

Anonymous — Mon, 07 Aug 2017 13:32:05 GMT

Still showing nothing.

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

LOAD

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

stevedark — Mon, 07 Aug 2017 13:41:57 GMT

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