Need to show Max date for each week Data

[mhmmd_srf]

Hi All,

I have a tabular report where I Have Week(that is Actually showing each week's Friday) and Date.

So Date is repeating for each week. I Want to show the data for Max date for each week.

Like below:

I want to show 443 for week 3/31/2017.

I am attaching my App. Could you please help me on this.

Thanks,

Sarif

[Gysbert_Wassenaar · Accepted solution]

Try this:

max(total <ItemNumber,CstWeekBeginDate> aggr(NODISTINCT

if(GetSelectedCount(DateSel)>0,sum({<DateSel={'$(=$(v1))'}>}OrderQuantityOutstanding),sum(OrderQuantityOutstanding))

,ItemNumber,CstWeekBeginDate,DT))

[sunny_talwar]

Another option

FirstSortedValue(TOTAL <DIM_ITEM.ItemNumber, DIM_ITEM.ItemDescription, CstWeekBeginDate>

Aggr(

if(GetSelectedCount(DateSel)>0,sum({<DateSel={'$(=$(v1))'}>}OrderQuantityOutstanding),sum(OrderQuantityOutstanding))

,DIM_ITEM.ItemNumber, DIM_ITEM.ItemDescription, CstWeekBeginDate, DT), -DT)

[mhmmd_srf]

Hey Sunny,

It is working. But it is repeating. I want one records  for each.

I need oval shaped circled record. How to do this.

Please help.

Thanks,

Sarif

[mhmmd_srf]

Hello Gysbert,

This is working. But I dont want repeating records.

I want one record for each week.

What would be the expression then.

Thanks,

Sarif

[sunny_talwar]

Try this

If(Max(TOTAL <DIM_ITEM.ItemNumber, DIM_ITEM.ItemDescription, CstWeekBeginDate> DT) = DT,

If(GetSelectedCount(DateSel) > 0, Sum({<DateSel={'$(=$(v1))'}>} OrderQuantityOutstanding), Sum(OrderQuantityOutstanding)))

[Gysbert_Wassenaar]

Then remove the DT dimension. That's making the rows unique.