Problem with bex query listing sales "this month" and "same month last year

[Former Member]

Hi,

I've created a query in BW BEx, where I have a mandatory variable for 0calmonth, and I list 0material and sales (in volume, liter). And I have a restricted key figure giving me sales (volume in liter) restricted with 0calmonth = variable - 12, to give me the sale for the same month the user select, previous year.

This seems to be working, the sum is correct, but when adding up the number for the sales in the month previous year, the numbers does not fit the sum in the end.

I think the report only lists materials with sales the month selected in the variable, if a material has not been sold that month, but in the same month last year, it's not listed in the reports, but it's added in the sum in the end.

Example.

We have three records like this:

material - calmonth - volume

101 - 01.2010 - 5

101 - 01.2011 - 8

201 - 01.2010 - 7

The report will give the following (calmonth variable = 01.2011)

Material - volume this month - volume same month last year

101 - 8 - 5

Sum 8 - 12

Any way to solve this issue? We noticed because we run BO - Webi on top of the BEx query, and BO sum's the values per material, and in webi the report would give us the sum 8 and 5.

Any input is appreciated.

Regards,

Øystein

[Former Member · Accepted solution]

I'm not entirely clear on what the error is but it looks as though the output was correct because material wasn't being used as a drill down. Have you drilled down by material? Getting 8 minus 12 is correct unless you drill down by material. In that case, it should be 8 minus 5 and 0 minus 7.

[Former Member · Accepted solution]

Hi,

thanks for your reply.

The error is that the report only seems to list materials that has been sold "this month", and if the same material was sold the same month last year, it will also list the volume for this.

But if the material is only sold in the same month last year, it's not listed in the report, but it's calculated into the sum at the end.

The report is listing material, which is why the error presents itself, as you said

Getting 8 minus 12 is correct unless you drill down by material. In that case, it should be 8 minus 5 and 0 minus 7.

The row with 0 - 7 is not shown, and my guess is that the report will only list materials that has sales this month (which has been selected in the variable)

Regards,

Øystein

Edited by: Oystein Gundersen on Mar 21, 2011 4:36 PM

[Former Member · Accepted solution]

Solved the issue.

The filter is set in the filter area, and for the same month last year, we're overriding the filter (constant selection (?)), but the filter seems to block anything that is not sold in the current month.

Removed the filter from the filter pane, and added it in the restricted key figure "this month" (key figure volume and 0calmonth = variable)

Now i'm getting rows where i have only had sale for the "same month last year" key figure.

[Former Member · Accepted solution]

If you are creating variance reports (e.g. this period vs. last period) then you can't use the variable in the characteristic restrictions. Instead, use it as you have done in the structure element otherwise you will get last year results filtered out.