topic Re: Removing a repeating row for calculation in QlikView

Removing a repeating row for calculation

kkkumar82 — Tue, 21 Mar 2017 09:33:41 GMT

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.

Re: Removing a repeating row for calculation

sunny_talwar — Tue, 21 Mar 2017 09:36:57 GMT

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

Re: Removing a repeating row for calculation

kkkumar82 — Tue, 21 Mar 2017 09:41:24 GMT

I want on DaysinQuarter and first value

First of all hi sunny after so many days

Re: Removing a repeating row for calculation

sunny_talwar — Tue, 21 Mar 2017 09:45:09 GMT

Try this expression:

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

Re: Removing a repeating row for calculation

kkkumar82 — Tue, 21 Mar 2017 10:37:32 GMT

This is working for this sample , thanks sunny

Re: Removing a repeating row for calculation

sunny_talwar — Tue, 21 Mar 2017 10:55:52 GMT

Not working in your original scenario?

Re: Removing a repeating row for calculation

kkkumar82 — Wed, 22 Mar 2017 01:45:58 GMT

yes, I will check this and let you know

Re: Removing a repeating row for calculation

kkkumar82 — Wed, 22 Mar 2017 09:40:00 GMT

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 ?

Re: Removing a repeating row for calculation

sunny_talwar — Wed, 22 Mar 2017 09:50:34 GMT

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]))

Re: Removing a repeating row for calculation

kkkumar82 — Wed, 22 Mar 2017 11:05:26 GMT

No luck sunny, its still giving the null values

Re: Removing a repeating row for calculation

sunny_talwar — Wed, 22 Mar 2017 11:06:35 GMT

Would you be able to share your app may be anonymizing the confidential stuff?

Re: Removing a repeating row for calculation

kkkumar82 — Wed, 22 Mar 2017 11:37:10 GMT

its a QlikSense App, I don't have access to the physical location of it

Re: Removing a repeating row for calculation

sunny_talwar — Wed, 22 Mar 2017 11:43:21 GMT

May be download the data and create a QlikView app?

Re: Removing a repeating row for calculation

kkkumar82 — Wed, 22 Mar 2017 12:45:42 GMT

The layout is in Qlikview, UI is in Qliksense, let me try what I can give you

Re: Removing a repeating row for calculation

sunny_talwar — Wed, 22 Mar 2017 12:59:23 GMT

Sounds good