Problem with TMVL and %TIME_SET%

[Former Member]

Hello!

Currently I'm testing TMVL in combination with %TIME_SET% and I'm a bit confused.

The user can define a time set in his package manager. After this, the script should delete all values, except the last month before the selected months. This works, if the user only selects one month. If the user selects 2 or more months, it doesn't work.

After reading the documentation for TMVL, I think it should work. Is it a bug? I know, there is another solution. But I have to know if this a bug or not

*XDIM_MEMBERSET CATEGORY = %CATEGORY_SET%
*XDIM_MEMBERSET TIME = BAS(2012.TOTAL)

*WHEN TIME
*IS TMVL(-1,%TIME_SET%)
  //nothing
*ELSE
*REC(EXPRESSION=((%VALUE% > 0)*0)+((%VALUE% < 0)*0))
*ENDWHEN

*COMMIT

Thanks!

Julian

[former_member186338]

P.S. About TMVL:

Sample:

%TIME_SET%=2012.05,2012.07

If we have in UJKT:

*XDIM_MEMBERSET TIME=TMVL(-1,%TIME_SET%)

After execute we will have:

LGX:

*XDIM_MEMBERSET TIME= 2012.04

The month before the first month in %TIME_SET% - single month!

TIMEID property has to be correctly maintained for base TIME members for TMVL to work correctly.

Vadim

[former_member186338]

Hi Julian,

Please, read

I will have to ask a lot of questions!!!

"After this, the script should delete all values, except the last month before the selected months. This works, if the user only selects one month. If the user selects 2 or more months, it doesn't work"

Can you explain EXACTLY the desired behavior:

What you want the user to select?

What you want to be cleared in term of months...

Vadim

P.S. This code is absolutely strange:

*REC(EXPRESSION=((%VALUE% > 0)*0)+((%VALUE% < 0)*0))

why not:

*REC(EXPRESSION=0)

Or to clear using RUNALLOCATION with FACTOR=0?

[Former Member]

Hello Vadim,

this is only an example. I cutted the original script extremely, because I only want to know, if this is a bug or the normal behaviour. No solution for my problem

I have a set of data. Something like that:

The user can choose a forecast and different months in the datamanager.

The script should delete all values for the choosen forecast, except the last month before the first of the selected month.

For example:

%TIME_SET%=2012.05,2012.07

TMVL(-1,%TIME_SET%) should return 2012.04

But it returns nothing or another month. If the user only selects 2012.05 it returns the correct month 2012.04

The TIMEID is correctly maintained. It's formatted like 20120200

Moreover we are using SAP BW 731 and BPC 801

[former_member186338]

Please provide test in UJKT with screenshot!

It was a real test in my previous message. And everything was fine!

SAP BW 731 and BPC 801 - SP for both???

And at the end - can you explain the business logic of this strange requirement?

[Former Member]

Hi Vadim,

seems to be a bit strange.... If I execute the logic script with UJKT, it behaves like expected. If I execute the same logic with DM, it multiplicates the wrong months, as you can see in my screenshots below ...

BW SP is 9

BPC SP is 3

Julian

[former_member186338]

Hi Julian,

BW SP is 9

BPC SP is 3

It's so terribly old that I can't comment on the results

Second - why do you continue writing this absolutely useless and strange REC:

*REC(EXPRESSION=((%VALUE% > 0)*0)+((%VALUE% < 0)*0)) ????

Third: never use COMMIT with WHEN/ENDWHEN - absolutely useless.

Forth - I do not understand the logic in general:

You allow user to select multiple dates, then you have fixed year in the code 2012.TOTAL, and your script have to clear all months of the fixed year excluding the month before the first selected by user moth... If the user will select months in 2013 then the whole 2012 will be cleared... etc...

Vadim

P.S. For DM package - please post the advanced DM script.

P.P.S. Can you also provide a detailed formula log of DM package run!

[Former Member]

It's really terrible old ... we are testing a releaseupgrade right now ... but in a big company it's a bit difficult to change a "running" system ....

It's only an example ... The REC expression is old, I could replace it, but it works. I replace this expression in my original code

The logic is senseless, it's only to reproduce the error in a small code with a few lines. If I would post the original code, you would have hundreds of lines, which have nothing to do with my problem.

Advanced DM script

PROMPT(SELECTINPUT,,,,"%CATEGORY_DIM%,%TIME_DIM%")

TASK(/CPMB/DEFAULT_FORMULAS_LOGIC,SUSER,%USER%)

TASK(/CPMB/DEFAULT_FORMULAS_LOGIC,SAPPSET,%APPSET%)

TASK(/CPMB/DEFAULT_FORMULAS_LOGIC,SAPP,%APP%)

TASK(/CPMB/DEFAULT_FORMULAS_LOGIC,SELECTION,%SELECTION%)

TASK(/CPMB/DEFAULT_FORMULAS_LOGIC,LOGICFILENAME,DELETE.LGF)

Formula log

LOG BEGIN TIME:2015-07-24 13:38:59

FILE:\ROOT\WEBFOLDERS\SALES_GERMANY\ADMINAPP\FC_INPUT\DELETE.LGF

USER:JUMEINER

APPSET:SALES_GERMANY

APPLICATION:FC_INPUT

[INFO] GET_DIM_LIST(): I_APPL_ID="FC_INPUT", #dimensions=6

ACCOUNT,CATEGORY,INPUT_OPP,MEASURES,RPTCURRENCY,TIME

#dim_memberset=2

CATEGORY:FC_2_10,1 in total.

TIME:2012.APR,2012.AUG,2012.DEC,2012.FEB,2012.JAN,...12 in total.

REC :(%VALUE%)*2

CALCULATION BEGIN:

QUERY PROCESSING DATA

QUERY TIME : 132.14 ms. 2  RECORDS QUERIED OUT.

QUERY REFERENCE DATA

CALCULATION TIME IN TOTAL :15.40 ms.

2  RECORDS ARE GENERATED.

CALCULATION END.

#dim_memberset=2

CATEGORY:FC_2_10,1 in total.

TIME:2012.APR,2012.AUG,2012.DEC,2012.FEB,2012.JAN,...12 in total.

REC :((%VALUE% > 0)*0)+((%VALUE% < 0)*0)

CALCULATION BEGIN:

QUERY PROCESSING DATA

QUERY TIME : 178.18 ms. 22  RECORDS QUERIED OUT.

QUERY REFERENCE DATA

CALCULATION TIME IN TOTAL :44.82 ms.

22  RECORDS ARE GENERATED.

CALCULATION END.

ENDWHEN ACCUMULATION: 24  RECORDS ARE GENERATED.

DATA TO WRITE BACK:

ACCOUNT CATEGORY INPUT_OPP RPTCURRENCY TIME SIGNEDDATA

OE FC_2_10 1000484472_10 EUR 2012.SEP 2000.00

OE FC_2_10 1000484472_10 TEUR 2012.SEP 2.00

OE FC_2_10 1000484472_10 EUR 2012.APR 0.00

OE FC_2_10 1000484472_10 TEUR 2012.APR 0.00

OE FC_2_10 1000484472_10 EUR 2012.AUG 0.00

OE FC_2_10 1000484472_10 TEUR 2012.AUG 0.00

OE FC_2_10 1000484472_10 EUR 2012.DEC 0.00

OE FC_2_10 1000484472_10 TEUR 2012.DEC 0.00

OE FC_2_10 1000484472_10 EUR 2012.FEB 0.00

OE FC_2_10 1000484472_10 TEUR 2012.FEB 0.00

OE FC_2_10 1000484472_10 EUR 2012.JAN 0.00

OE FC_2_10 1000484472_10 TEUR 2012.JAN 0.00

OE FC_2_10 1000484472_10 EUR 2012.JUL 0.00

OE FC_2_10 1000484472_10 TEUR 2012.JUL 0.00

OE FC_2_10 1000484472_10 EUR 2012.JUN 0.00

OE FC_2_10 1000484472_10 TEUR 2012.JUN 0.00

OE FC_2_10 1000484472_10 EUR 2012.MAR 0.00

OE FC_2_10 1000484472_10 TEUR 2012.MAR 0.00

OE FC_2_10 1000484472_10 EUR 2012.MAY 0.00

OE FC_2_10 1000484472_10 TEUR 2012.MAY 0.00

OE FC_2_10 1000484472_10 EUR 2012.NOV 0.00

OE FC_2_10 1000484472_10 TEUR 2012.NOV 0.00

OE FC_2_10 1000484472_10 EUR 2012.OCT 0.00

OE FC_2_10 1000484472_10 TEUR 2012.OCT 0.00

24  RECORDS HAVE BEEN WRITTEN BACK.

WRITING TIME :102.67  ms.

SCRIPT RUNNING TIME IN TOTAL:0.67 s.

LOG END TIME:2015-07-24 13:39:00

File path: \ROOT\WEBFOLDERS\SALES_GERMANY\FC_INPUT\PRIVATEPUBLICATIONS\JUMEINER\tempfiles\20150724133859\DELETE.LOG

[former_member186338]

"It's really terrible old ... we are testing a releaseupgrade right now ... but in a big company it's a bit difficult to change a "running" system ...." - It's your main issue! Hundreds of bugs were corrected since SP03. You can spend hours trying to find the reason... And I can't reproduce your strange results...

Please test as DM script:

*XDIM_MEMBERSET CATEGORY = %CATEGORY_SET%

*XDIM_MEMBERSET TIME = TMVL(-1,%TIME_SET%) //In the prompt screen set 2012.SEP,2012.OCT

*WHEN TIME

*IS *

*REC(EXPRESSION=%VALUE% + 100)

*ENDWHEN

And post the detailed log - I will see the TIME dimension member as a result of:

TMVL(-1,2012.SEP,2012.OCT)

Vadim

[Former Member]

LOG BEGIN TIME:2015-07-24 15:00:04

FILE:\ROOT\WEBFOLDERS\SALES_GERMANY\ADMINAPP\FC_INPUT\DELETE.LGF

USER:JUMEINER

APPSET:SALES_GERMANY

APPLICATION:FC_INPUT

[INFO] GET_DIM_LIST(): I_APPL_ID="FC_INPUT", #dimensions=6

ACCOUNT,CATEGORY,INPUT_OPP,MEASURES,RPTCURRENCY,TIME

#dim_memberset=2

CATEGORY:FC_4_8,1 in total.

TIME:2012.SEP,1 in total.

REC :%VALUE% + 100

CALCULATION BEGIN:

QUERY PROCESSING DATA

QUERY TIME : 152.96 ms. 2  RECORDS QUERIED OUT.

QUERY REFERENCE DATA

CALCULATION TIME IN TOTAL :14.54 ms.

2  RECORDS ARE GENERATED.

CALCULATION END.

ENDWHEN ACCUMULATION: 2  RECORDS ARE GENERATED.

DATA TO WRITE BACK:

ACCOUNT CATEGORY INPUT_OPP RPTCURRENCY TIME SIGNEDDATA

OE FC_4_8 1000484472_10 EUR 2012.SEP 1100.00

OE FC_4_8 1000484472_10 TEUR 2012.SEP 101.00

2  RECORDS HAVE BEEN WRITTEN BACK.

WRITING TIME :155.48  ms.

SCRIPT RUNNING TIME IN TOTAL:0.47 s.

LOG END TIME:2015-07-24 15:00:05

File path: \ROOT\WEBFOLDERS\SALES_GERMANY\FC_INPUT\PRIVATEPUBLICATIONS\JUMEINER\tempfiles\20150724150004\DELETE.LOG

[former_member186338]

Absolutely strange!

Let's test:

*XDIM_MEMBERSET CATEGORY = %CATEGORY_SET%

*XDIM_MEMBERSET TIME = %TIME_SET% //In the prompt screen set 2012.SEP,2012.OCT

//here in the log will be TIME:2012.SEP,2012.OCT,2 in total.

*WHEN TIME

*IS *

*REC(EXPRESSION=%VALUE%)

*ENDWHEN

*XDIM_MEMBERSET TIME = TMVL(-1,%TIME_SET%)

//here in the log will be TIME:2012.SEP,1 in total.??

*WHEN TIME

*IS *

*REC(EXPRESSION=%VALUE% + 100)

*ENDWHEN

[Former Member]

As I said

LOG BEGIN TIME:2015-07-24 15:11:44

FILE:\ROOT\WEBFOLDERS\SALES_GERMANY\ADMINAPP\FC_INPUT\DELETE.LGF

USER:JUMEINER

APPSET:SALES_GERMANY

APPLICATION:FC_INPUT

[INFO] GET_DIM_LIST(): I_APPL_ID="FC_INPUT", #dimensions=6

ACCOUNT,CATEGORY,INPUT_OPP,MEASURES,RPTCURRENCY,TIME

#dim_memberset=2

CATEGORY:FC_5_7,1 in total.

TIME:2012.OCT,2012.SEP,2 in total.

REC :%VALUE%

CALCULATION BEGIN:

QUERY PROCESSING DATA

QUERY TIME : 124.55 ms. 4  RECORDS QUERIED OUT.

QUERY REFERENCE DATA

CALCULATION TIME IN TOTAL :16.40 ms.

4  RECORDS ARE GENERATED.

CALCULATION END.

ENDWHEN ACCUMULATION: 4  RECORDS ARE GENERATED.

DATA TO WRITE BACK:

ACCOUNT CATEGORY INPUT_OPP RPTCURRENCY TIME SIGNEDDATA

OE FC_5_7 1000484472_10 EUR 2012.OCT 1000.00

OE FC_5_7 1000484472_10 TEUR 2012.OCT 1.00

OE FC_5_7 1000484472_10 EUR 2012.SEP 1000.00

OE FC_5_7 1000484472_10 TEUR 2012.SEP 1.00

4  RECORDS HAVE BEEN WRITTEN BACK.

WRITING TIME :131.23  ms.

[INFO] GET_DIM_LIST(): I_APPL_ID="FC_INPUT", #dimensions=6

ACCOUNT,CATEGORY,INPUT_OPP,MEASURES,RPTCURRENCY,TIME

#dim_memberset=2

CATEGORY:FC_5_7,1 in total.

TIME:2012.SEP,1 in total.

REC :%VALUE% + 100

CALCULATION BEGIN:

QUERY PROCESSING DATA

QUERY TIME : 93.21 ms. 2  RECORDS QUERIED OUT.

QUERY REFERENCE DATA

CALCULATION TIME IN TOTAL :9.80 ms.

2  RECORDS ARE GENERATED.

CALCULATION END.

ENDWHEN ACCUMULATION: 2  RECORDS ARE GENERATED.

DATA TO WRITE BACK:

ACCOUNT CATEGORY INPUT_OPP RPTCURRENCY TIME SIGNEDDATA

OE FC_5_7 1000484472_10 EUR 2012.SEP 1100.00

OE FC_5_7 1000484472_10 TEUR 2012.SEP 101.00

2  RECORDS HAVE BEEN WRITTEN BACK.

WRITING TIME :69.79  ms.

SCRIPT RUNNING TIME IN TOTAL:0.63 s.

LOG END TIME:2015-07-24 15:11:44

File path: \ROOT\WEBFOLDERS\SALES_GERMANY\FC_INPUT\PRIVATEPUBLICATIONS\JUMEINER\tempfiles\20150724151144\DELETE.LOG

[Former Member]

Tested the same in another environment with a younger release:

.... same behaviour

[former_member186338]

Ups, I can see a bug:

In the log:

TIME:2012.OCT,2012.SEP,2 in total.

have to be:

TIME:2012.SEP,2012.OCT,2 in total.

Looks like members in %TIME_SET% are sorted alphabetically before TMVL is applied!

With TIME members like 2012.09,2012.10 - it's not an issue, but with SEP, OCT - you have strange results!

Example: 2012.FEB,2012.MAR - correct result

2012.MAR,2012.APR - incorrect result -> 2012.APR,2012.MAR

Some corrections were done for TMVL in CPMBPC 801 SP11... test notes!

Vadim