Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
LINKEDIN LIVE: Democratizing data to enhance customer-centricity. JULY 29TH REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
zakpullen
Contributor III
Contributor III

Expression not producing expected results

Hi,

I have data similar to the fake table below. The start and end dates are the period for which a doctor is assigned to a RotaID. For any given month (MonthYear) I want to aggregate wte * rotadays for each RotaID. Rotadays are the number of days a doctor is assigned to a RotaID in the given month. For example, rotadays for May 2020 in the first row would be 4.

RotaTbl.JPG

I have a calculation that works fine if there is a single doctor against a RotaID in a specific month:

AGGR(Count(Distinct RotaDate)*sum(RotaWTE),RotaID,RotaMonthYear)

However it doesn't work if 2 doctors are against a RotaID in the same month. If we take RotaID Anaes SPR2 and August 2019 as an example.

The correct calculation should be:

Michael Wood: 31 * 0.7 = 21.7

Michelle Brown: 25 * 0.8 = 20

The expression should return 41.7 (21.7 + 20)

However, it is not performing each calculation seperately, and aggregating the results. It is first adding the wte together (0.7 + 0.8 = 1.5), and then multiplying that by the first count of rotadays (31) to produce 46.5.

I've experimented with my expression, but just cannot nail it. I tried including a RowID, but that didn't work.

Is there a solution? I don't think this can be done in the script as a calendar is created, and an interval table used to link it to the data table.

 

Many thanks.

Labels (2)
1 Solution

Accepted Solutions
sunny_talwar

Try this

Sum(Aggr(
    Count(DISTINCT RotaDate) * Sum(RotaWTE)
, RotaID, RotaMonthYear, [First Name], [Last Name]))

View solution in original post

2 Replies
sunny_talwar

Try this

Sum(Aggr(
    Count(DISTINCT RotaDate) * Sum(RotaWTE)
, RotaID, RotaMonthYear, [First Name], [Last Name]))

View solution in original post

zakpullen
Contributor III
Contributor III
Author

That was quick, and it works!

 

Thank you so much.

 

Zak