topic Re: Need to show Max date for each week Data in QlikView

Need to show Max date for each week Data

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

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 — Wed, 19 Apr 2017 17:37:40 GMT

Try this:

max(total <ItemNumber,CstWeekBeginDate> aggr(NODISTINCT

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

,ItemNumber,CstWeekBeginDate,DT))

sunny_talwar — Wed, 19 Apr 2017 18:28:05 GMT

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 — Thu, 20 Apr 2017 02:48:26 GMT

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 — Thu, 20 Apr 2017 02:49:23 GMT

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 — Thu, 20 Apr 2017 08:59:47 GMT

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 — Thu, 20 Apr 2017 17:19:55 GMT

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