Skip to Content
0
Former Member
Feb 12, 2010 at 02:39 PM

Query problem with accumulated key figures

48 Views

Hi BI Gurus!

I have a report problem that I hope you can help me with!

In my report I have 2 key figures. One for accumulated revenue previous year (KF1) and one for accumulated revenue current year (KF2). Both key figures should be presented in a graph in monthly buckets.

January figures from both the key figures shoule be presented in the same bucket. Therfore I can't use 0CALYEAR since we have the "year" information in there. Instead I'm using 0CALMONTH2 which is only two digits, 01 for January. That way I can map figures from both previous year and current year in the same bucket.

I need the figures to be accumulated and this is what I have problem with. When I run the report today in February 2010 it looks like this:

Month KF1 KF2

01 10 15

02 10 20

03 15

04 10

05 20

06 10

07 10

08 15

09 15

10 20

11 20

12 10

This is how I would like the report to look like:

Month KF1 KF2

01 10 15

02 20 35

03 35

04 45

05 65

06 75

07 85

08 100

09 115

10 135

11 155

12 165

I have tried to use the setting "accumulated" for the key figures but then I get this result:

Month KF1 KF2

01 10 15

02 20 35

03 35 35

04 45 35

05 65 35

06 75 35

07 85 35

08 100 35

09 115 35

10 135 35

11 155 35

12 165 35

Since the KF2 is revenue for current year and I run the report in February I don't want any figures to be displayed in Mars...

I have tried to restrict the key figures by 0CALMONTH2 and 0CALYEAR. The KF1 is havein a restriction to only show values for 0CALYEAR - 1 and an interval for 0CALMONTH2 from JAN - DEC.

The KF2 is having a restriction to only show values in the interval "first month in year - current month" (in this example JAN - FEB) for 0CALMONTH2. And current year for 0CALYEAR.

Despite my restrictions for KF2 the numpers repeats itself for every month...

Any suggestion how I can resolve this?

Best regards

Anders Florin