Higher Level value is behaving Odd

[mhmmd_srf]

Hi All,

I have a Table to show Current Month Actual Sales, Current Month Planned Sales, Current Year Actual Sales and Current Year Planned Sales in Item Level. My issue is in Current Year Planned Sales.

If we check for One particular Item. Item Level data is not Matching. Now If we add Month in dimension. Month wise value is coming correct and summation of all 12 month is also coming correct. But when I remove the Month. It is messing up.

As per my data Current year= 2017 and Current Month = 6.

I am attaching the qvw file.

Please check and help.

Thanks,

Sarif

[tresesco]

Try removing DISTINCT keyword from the expression.

[mhmmd_srf]

No wworking sir...

[tresesco]

I am getting it like:

using exp: =sum({<CurrentFiscalYearFlag = {1}>}  SalesPlanned)

Isn't this your desired output?

[mhmmd_srf]

Tresesco Sir,

We need this Distinct. Else individual data is also not matching with DB. As here we have Two fact table (Actual and Plan in different granularity).

Using distinct above is matching with DB. Only we need to Remove CstFiscalPeriod but it should show 89409.01 for Item no 024091.

Please help.

Thanks,

Sarif

[tresesco · Accepted solution]

What if you use:

=Sum(Aggr(sum({<CurrentFiscalYearFlag = {1}>}  DISTINCT SalesPlanned), CstFiscalPeriod))