topic Re: Max Value by Date and Member in QlikView

Max Value by Date and Member

nelson_sm — Wed, 25 Nov 2020 16:16:04 GMT

I'm having a problem with getting an expected total in a table for the following expression.

max(aggr(sum(aggr(max(Days),MemberId, Month)),Month))

Excel data set is attached.

Below is a tabular view of what we are expecting.

Total should be (88+4+52+52+6+26+18) = 246

MemberId	Jun	July	Aug	Sep as of 9/15
1	11 days	42 days	73 days	88 days
3		4
2		6	37	52
4		6	37	52
7			6
5			11	26
6			3	18
Values	11	(42+4+6+6) = 58	(73+37+37+6+11+3) = 167	(88+52+52+26+18)/5 = 236

Any ideas how to accomplish this?

Thanks in advance.

Max Value by Date and Member

Vegar — Sun, 11 Oct 2020 05:43:41 GMT

As you don't have any values for member 3 and 6 in September you will need to create a expression that looks outside each table cell member and month boundary.

Maybe something like this.

Aggr( Rangemax( Above( Max(Days),0,rowno())),MemberId,Month)

Another alternative to avoid the use of rangemax above is to calculate the values for missing months/dates in the script.

Re: Max Value by Date and Member

nelson_sm — Tue, 13 Oct 2020 20:27:38 GMT

Thanks Vegar.

Unfortunately, I'm still not getting the total of 246.

Re: Max Value by Date and Member

Kushal_Chawda — Tue, 13 Oct 2020 23:37:46 GMT

@nelson_sm I don't think so it is possible to get the customized total with max value and keeping the base value as sum. Because total always evaluated based on base expression. Like currently expression which you are using giving you max value evaluated for each Month as final aggregation is Max.

But you can probably tweak it to get the desired results. see the attached

Re: Max Value by Date and Member

nelson_sm — Thu, 15 Oct 2020 19:07:09 GMT

Hi Kush,

Your tweak was a great idea although when I modified the calculation to as follows it will not pick up the expression for Dim 2. Any idea why?

Pick(Dim,
sum(aggr(Max(Days),MemberId)),
Max(aggr(sum(aggr(Max(Days),MemberId,Month)),Month))
)

Thank for your feedback.

Re: Max Value by Date and Member

sunny_talwar — Thu, 15 Oct 2020 19:16:10 GMT

@nelson_sm to do this, add Dim to your Aggr() functions

Pick(Dim, sum(aggr(Max(Days),MemberId, Dim)), Max(aggr(sum(aggr(Max(Days),MemberId,Month, Dim)),Month, Dim)) )

But you can also try this without using Dim

If(Dimensionality() = 0, Sum(Aggr(Max(Days), MemberId)), Max(Aggr(Sum(Aggr(Max(Days), MemberId, Month)), Month)) )

Used the sample provided by @Kushal_Chawda (thanks)

Re: Max Value by Date and Member

nelson_sm — Thu, 15 Oct 2020 20:03:03 GMT

Thanks so much Sunny! Worked like a charm.