Removing a repeating row for calculation

[kkkumar82]

In  the attached excel for column DaysinQuarter for the rows 19, 38 there are two rows broken by the FiscalPeriod, I need to build a table

with DaysinQuarter Dimension where in I don't want the two values to be aggregated.

for eg,

For DaysinQuarter for 19th,38th row I want to consider only the first row. I achieved it with DaysinQuarter and Fiscal period as dimension

but want it with out using DaysinQuarter.

Attaching sample data and Qvw file.

[sunny_talwar]

So what dimension do you need? and what value? Sum of the two 19 rows or the first value?

[kkkumar82]

I want on DaysinQuarter and first value

First of all hi sunny after so many days

[sunny_talwar · Accepted solution]

Try this expression:

FirstSortedValue(Aggr(Sum(Forecast), DaysinQuarter, [Fiscal Period]), Aggr([Fiscal Period], DaysinQuarter, [Fiscal Period]))

[kkkumar82]

This is working for this sample , thanks sunny

[sunny_talwar]

Not working in your original scenario?

[kkkumar82]

yes, I will check this and let you know

[kkkumar82]

Hi Sunny,

As per the excel what I have attached your expression is perfectly matching but when I am testing with my original expression where the dimension is the same like DaysinQuarter but the expression is as follows

Sum({<[Fiscal Qtr-year]={'$(vMaxFiscalQuarter)'},[Divisional Group]={'GYN Surgical'},Type ={'USD'},

GEO_NEW2={'US'}>}[Total Surgical Month Forecast])//))

/

Max({<[Fiscal Qtr-year]={'$(vMaxFiscalQuarter)'},[Divisional Group]={'GYN Surgical'},Type ={'USD'},

GEO_NEW2={'US'}>}DaysinMonth)

I added your expression like this but the chart is not rendering at all, I am getting all null values

FirstSortedValue(Aggr(Sum({<[Fiscal Qtr-year]={'$(vMaxFiscalQuarter)'},[Divisional Group]={'GYN Surgical'},

Type ={'USD'},GEO_NEW2={'US'}>}[Total Surgical Month Forecast]),DaysintoQuarter,FiscalPeriod),

Aggr(Only({<[Fiscal Qtr-year]={'$(vMaxFiscalQuarter)'},[Divisional Group]={'GYN Surgical'},Type ={'USD'},

GEO_NEW2={'US'}>}FiscalPeriod),DaysintoQuarter,FiscalPeriod)),

/

Max({<[Fiscal Qtr-year]={'$(vMaxFiscalQuarter)'},[Divisional Group]={'GYN Surgical'},Type ={'USD'},

GEO_NEW2={'US'}>}DaysinMonth)

any guess ?

[sunny_talwar]

May be this:

FirstSortedValue(Aggr(

Sum({<[Fiscal Qtr-year]={'$(vMaxFiscalQuarter)'},[Divisional Group]={'GYN Surgical'},Type ={'USD'},

GEO_NEW2={'US'}>}[Total Surgical Month Forecast])//))

/

Max({<[Fiscal Qtr-year]={'$(vMaxFiscalQuarter)'},[Divisional Group]={'GYN Surgical'},Type ={'USD'},

GEO_NEW2={'US'}>}DaysinMonth)

, DaysinQuarter, [Fiscal Period]), Aggr([Fiscal Period], DaysinQuarter, [Fiscal Period]))

[kkkumar82]

No luck sunny, its still giving the null values