Creating a new field dependent on several variables

[caccio88]

Hi everyone,

what I'm looking for is to create a new field that is the sum of next three months and dependent on the value of some other fields...

I try to explain it.

First of all I don't know if that is possible or better to create it in script or in the front end, for me I think would be the same.

I attach here the excel example of my database.

We have "Date", "YearMonth" (field calculated from the Year and the Month of the field Date), "ITEM CODE", "Stock" (value), "CMV" (value) and at the end "CMV_3M" which is the field I want to create.

As you can see in my excel, "CMV_3M" depends from different other fields: it should be the sum of the "CMV" of the next three months per each "ITEM CODE", but only if in that month "Stock" is different from zero, otherwise the value will be zero.

Is that possible?

Am I clear enough?

sunindia‌ swuehl‌ gwassenaar‌

Thank u all.

Filiberto

[sunny_talwar]

To answer you first question:

There is a slight difference between the two expressions

-> =Sum(Aggr(Sum({<Stock ={"<>0"} >} LAST3MonCMV), YearMonth, [ITEM CODE])) -> Will always show all rows where stock is not equal to 0 regardless of selections in stock field

-> =Sum(Aggr(Sum({<Stock -={0}>} LAST3MonCMV), YearMonth, [ITEM CODE])) -> based on selection in stock field this may filter out all the stock which are not selected, but will always exclude 0

To answer your second question, I will have to take a deeper look at the code and see what is that we are trying to do here.

[caccio88]

sunindia‌

Maybe I wasn't so clear before...

My problem was, I don't have the field "YearMonth" as a natural field in my real scenario and your solution was created on the format of that.

So I've just created a derivated field from the "Date" field and now my app seems to work.

Table:

LOAD Date,

  AutoNumber(Year(Date)*100 + Month(Date)&'|'&[ITEM CODE], 'Key')                     as Key,

   Year(Date)*100 + Month(Date)                                                     as YearMonthDEF,

    [ITEM CODE],

    Stock,

    CMV

FROM

(ooxml, embedded labels, table is Foglio1);

AggregatedTable:

LOAD

AutoNumber(YearMonthDEF&'|'&[ITEM CODE], 'Key')                     as Key,

  RangeSum(If([ITEM CODE] = Previous(Previous(Previous([ITEM CODE]))), Previous(Previous(Previous([TotalCMV])))),

  If([ITEM CODE] = Previous(Previous([ITEM CODE])), Previous(Previous(TotalCMV))),

  If([ITEM CODE] = Previous([ITEM CODE]), Previous(TotalCMV))) as [LAST3MonCMV];

LOAD YearMonthDEF,

  [ITEM CODE],

  Sum(CMV) as TotalCMV

Resident Table

Group By YearMonthDEF,[ITEM CODE]

Order By [ITEM CODE], YearMonthDEF desc;

Than I can also use all the derivated field of my "MasterCalendar" as Dimension of analysis.

Please check if my solution is good enough.

Thank u so much.

[sunny_talwar]

Ya this seems like a good work around and seems like its working out pretty well, right? Have you tried making selections and making sure that numbers make sense? I am not so familiar with the data and would have to rely on you for all that information.

But on the whole, good job on getting it fixed

[caccio88]

Hi stalwar1‌,

I tried using a different solution for this case and the final result  seems doesn't change.

From "Sum(Aggr(Sum({<Stock ={"<>0"} >} LAST3MonCMV), YearMonthDEF, [ITEM CODE]))" to "Sum({<Stock ={"<>0"} >} LAST3MonCMV)".

Take a look to the new objects in red.

The final results doesn't change and I think that we have grouped by YearMonthDEF and ITEM CODE in the script yet, building the "LAST3MonCMV" field. Don't you are agree?

Do you think that could be any problem using my new expression?

Thank u a lot.

Filiberto

[caccio88]

I can't understand if the attachment can be viewed by you....

I see a strange file extension...

[sunny_talwar]

It works, I just needed to add .qvw at the end

[caccio88]

Ok, nice!

Waiting for your opinion about my new development..

[sunny_talwar]

Yes Filiberto‌, I think there is no difference and I don't think there should be any problem down the line as well. The two expression should always return the same value (for this particular dimension). Things might change if you plan to use a cycle group or a different dimension. But for YearMonthDEF, it doesn't make sense to use Aggr() function (for your current requirement)

[sunny_talwar]

I would be interested in hearing Stefan's opinion on this as well. He seems to be an expert in figuring out differences in expressions that sometimes look a like to me