Summary Data Issue

[mhmmd_srf]

Hi All,

I have an requirement to show Current Month Plan Unit and Current Year Plan Units.

If user select Year = 2016 and Month = 5

then CMPlan Unit will show = Plan Units for Month 5 and CYPlan Unit should show plan units for 2016.

If I put Month, then it is showing Month Level data correct. But I need to show data in Item Level. Item level data is not showing correct data.

I am attaching qvw file. Please check the table in Sheet2 and the issue is in CYPlanUnits.

for 2016 and Month =5 it should come as 11246, but it is coming as 8482.

we are using distinct, but that is required.

Please help.

Thanks,

Sarif

[vishsaggi]

Why you have to get 11246? Your Units Planned for Year 2016 has below possible data ? Do we have to consider something else? For year 2016 i have below units planned which sum up to - 8482.

844

890

907

922

937

952

968

984

1078

[rubenmarin]

Hi Mohammad, having to sum 'distinct' values can lead to this kind of issues, where two same values that are equal but have to be summed, you can get a working expression using:

=sum({<CstFiscalPeriod = ,CstFiscalYear = {'$(=$(vMaxYear))'}>}

aggr(sum({<CstFiscalPeriod = ,CstFiscalYear = {'$(=$(vMaxYear))'}>} DISTINCT UnitsPlanned),ItemItemNo,ItemDesc,CstFiscalYear, CstFiscalPeriod))

This way same values in different months can be summed.

If you can avoid the duplicated rows and just do a sum (without 'distinct') it will be better.

[vishsaggi]

Thanks Ruben, I got what you mean.

[mhmmd_srf]

Hello Ruben sir..

seems this is working.

It would be really helpful if you can explain this expression.

I just copied blindly to meet the delivery deadline. 

But I want to make this my own next time. So need your guidance.

I will check thoroughly for other Item no also and will let you know.

Thanks a lot sir..for your time and help..

Thanks,

Sarif

[rubenmarin]

Hi Mohammad, I added the period field to the aggr, so it sums the distinct values by each year and month, this is to sum equal values in different months.

Having this field in aggr will cause that the expression will be affected by selections in the Period field, to avoid this I also need to add the same set analysis to the external sum, so it does the operation for the max year and ignores selections in month.

[mhmmd_srf]

Ruben Sir,

I got the first part. But the external Set analysis is not clear to me.

Could you please explain it.

Thanks a lot for your time.

Thanks,

Sarif