cancel
Showing results for 
Search instead for 
Did you mean: 

How to keep 12 periods with 2 restricted key figures?

Former Member
0 Kudos

Hi There!

Hope someone in this forum could help us.

We have two restricted key figure on specific time period and version (actuals & planned data), let say key figure KF1 restricted from Period 1 to 3 and key figure KF2 restricted from Period 4 to 12.

We would like to get following report


	[KEY FIGURE]	KF1			|KF2
	[PERIOD]	1	2	3	|4	5	6	7	8	9	10	11	12

[0MATERIAL]		
A001			30	30	30	 30	50	...
A002			25	0	40	 40	...
...			20	56	45	 20	...

Unfortunately, query designer is always combining all periods with all key figures: ie we've got additional periods 4, 5, 6, 7, 8, 9, 10, 11, 12 for KF1 and 1, 2, 3 for KF2 - total 24 periods.

We tried to remove 0, unfortunately we've got unwanted behaviour: on some material some periods are empty like A002 for period 2, if we restrict on A002, period 2 disappeared.

We would like to get always 12 periods.

Could you help us? Do you have any idea? or suggestion? many thank for your help.

Kind Regards

Olivier

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

First of all, restrict KF1 with a range from 1 to a variable based on period (in taht case value is 4).

In KF2 do restriction with range from Variable with offsett 1 (means 5) to fixed value 12.

Hope it's clear.

Regards.

PS: assign points is the way to say thanks

Answers (5)

Answers (5)

Former Member
0 Kudos

Found another way.

Former Member
0 Kudos

What was the solution?

Former Member
0 Kudos

Thank you all of you. That's very helpful.

Just to be more precise. KF1 & KF2 are restrited to 0AMOUNT key figure & a period type which indicate which period is closed and which period is opened.

Therefore KF1 is restricted on 0AMOUNT and Period Type C (=closed) and KF2 is restricted on 0AMOUNT and Period Type O (=closed).

Then I need to split KF1 & KF2 total amount by Period.

System is generating 24 periods instead of 12 because it is generating wrong combination.

Note: Figures are wrong and typed randomly - this is just to explain issue.

I tried Exclusion, Variable and still same issue. Using another structure is not possible because in my query, I already have one (I tried to be simple in this thread).

Thank you anyway for your very interesting feedback.

I still have issue, any other idea?

Kind Regards

Olivier

Former Member
0 Kudos

Hi Olivier,

Take all periods under each KF,use "Exclude from selection" for periods that are not required.

Former Member
0 Kudos

the best way to do this is using cell definition ..

use two structure

where in in the cell definition you do the required logic

for ex

KF1(mnt1) KF1(mnt2) KF1(mnt3) KF2(mnt4) KF(mnt5) .....

material

hope this helps.

Former Member
0 Kudos

Thank you for your quick reply.

I tried your suggestion, now I've got following results:

KF1	1	20
	2	30
	3	40
	4	Empty cell
	5	Empty cell
	6	Empty cell
	7	Empty cell
	8	Empty cell
	9	Empty cell
	10	Empty cell
	11	Empty cell
	12	Empty cell
KF2	1	Empty cell
	2	Empty cell
	3	Empty cell
	4	50
	5	60
	6	0
	7	30
	8	20
	9	0
	10	33
	11	32
	12	32

How to remove empty cells? many thanks.

Kind Regards

Olivier

Former Member
0 Kudos

Sorry, i don't understand; i understood you wuold like to have only total 90 for KF1 and 257 for KF2.

What's exactly your requirement? What's the diffrence from KF1 and KF2?

Regards