Measure using P() and AGGR

[hopkinsc]

Hi all,

I am struggling to get a measure to work using the P() function with AGGR. This is my measure

sum(aggr(
Sum({1<HSCKEY=P(HSCKEY),ProductGroup -= {'17','18','19'},TransactionBreak = {'1'}>} TransactionNetWeight),
MasterLocation,Room,TransactionNumber))

and it returns this..

These are the incorrect values, they are only correct if you select an individual date..

I've tried numerous things like adding the set analysis into the AGGR and the overall SUM but I can't get it to work.

The first date in the table 1/1/2025 is correct though, without selecting it. Its all other dates that are wrong. 

Can anyone help please? 

I've attached a sample. 

Thanks very much

[rubenmarin]

Hi, to keep the values by Date you'll also need to include Date dimension in the Aggr()

sum(aggr(
Sum({1<HSCKEY=P(HSCKEY),ProductGroup -= {'17','18','19'},TransactionBreak = {'1'}>} TransactionNetWeight),
Date,MasterLocation,Room,TransactionNumber))

[hopkinsc]

Good morning, 

Thanks for the reply, this is the first thing i tried, but doesn't give me the required output unfortunately. 

When i add Date into the AGGR, the values change but are still wrong. The correct values only display without Date in the AGGR and when i make a selection on Date.

i.e. 

with no date selected, 3rd Jan shows 267,142. which is the incorrect value

when i select the date, the values changes to 1,838,876 which is the correct value, but i want this value to be displayed when no selections have been made. 

This currently only works for the 1st Jan value, and i don't know why..

There's obviously something wrong with the measure, I just can't figure out what. 

Thanks

[rubenmarin]

Hi, when I add date to your sample app it looks like this:

And when I selct a Date the value is the same

The behaviour you describe is what happens when you use a table with a dimension that doesn't exists in the aggr, in that case when the atggr dimesnion combiations could match differnt dimension values, the aggr assign combinations to the first value (in this case the 1/1), and removes the data from other dates, so only when you select a date those values that matches many different dates goes to that date.

So double check you really add the Date dimension to aggr, that higher value on 1/1 is very suspiciosuis that the aggr is not using Date as dimension.

This works on the sample app:

sum(aggr(
Sum({1<HSCKEY=P(HSCKEY),ProductGroup -= {'17','18','19'},TransactionBreak = {'1'}>} TransactionNetWeight),
Date,MasterLocation,Room,TransactionNumber))

[hopkinsc]

Hi, 

When i add Date in the AGGR the values are not right. Obviously, when i then select a date, the values stays as it was without a date selected. That is what i want, but the values are wrong. 

Here are the results of the table with and without Date added into the AGGR

but these are the values I am expecting to see. 

I only get the correct values when each date is selected individually (without Date in the AGGR)

[marcus_sommer]

Before struggling further with UI expressions make sure that the underlying data are like you expect them and that the used data-model and table-associations are suitable for your wanted views.

It may not mandatory easy but I would start with a table-box with all fields from the object (used in dimensions and expressions) as well as within any selections + an unique record-identifier - if none exists you could create one with recno() and/or rowno(). I wouldn't be surprised if there are any unexpected data - duplicates, missing keys, null, ...