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

[swuehl]

You could start with a front end solution. Create a chart with dimensions Item Code and YearMonth and expressions:

=Sum(Stock)

=Sum(CMV)

=If(Sum(Stock)>0, Rangesum(Below(Sum(CMV),1,3)),0)

ITEM CODE	YearMonth	Sum(Stock)	Sum(CMV)	Sum(CMV_3M)	If(Sum(Stock)>0, Rangesum(Below(Sum(CMV),1,3)),0)
		4138	7643	12624	0
ABC	201503	282	20	1117	1117
ABC	201504	436	620	1356	1356
ABC	201505	20	10	1831	1831
ABC	201506	0	487	0	0
ABC	201507	0	859	0	0
ABC	201508	310	485	1334	1334
ABC	201509	388	667	667	667
ABC	201510	388	667	0	0
XYZ	201503	282	20	1130	1130
XYZ	201504	436	620	1369	1369
XYZ	201505	0	23	0	0
XYZ	201506	0	487	0	0
XYZ	201507	510	859	1819	1819
XYZ	201508	310	485	1334	1334
XYZ	201509	388	667	667	667
XYZ	201510	388	667	0	0

[sunny_talwar · Accepted solution]

May be this, if you want to do this in the script:

Table:

LOAD Date,

    YearMonth,

    [ITEM CODE],

    Stock,

    CMV

FROM

[Filtro CMV per stock 0 con CMV_v2.xlsx]

(ooxml, embedded labels, table is Foglio1);

AggregatedTable:

LOAD *,

  RangeSum(If([ITEM CODE] = Peek('ITEM CODE', -3), Peek('TotalCMV', -3)),

  If([ITEM CODE] = Peek('ITEM CODE', -2), Peek('TotalCMV', -2)),

  If([ITEM CODE] = Peek('ITEM CODE', -1), Peek('TotalCMV', -1))) as [LAST3MonCMV];

LOAD YearMonth,

  [ITEM CODE],

  Sum(CMV) as TotalCMV

Resident Table

Group By [ITEM CODE], YearMonth

Order By [ITEM CODE], YearMonth desc;

Straight Table:

Dimension: YearMonth

Expression: =Sum(Aggr(If(Sum(Stock) > 0, LAST3MonCMV), YearMonth, [ITEM CODE]))

[caccio88]

Hi sunindia‌,

I love your solution, but I see that now we have a "$Sin 1 Table". Could it be a problem in any case?

Thank you so much for your help.

[sunny_talwar]

It should not be a problem, but there are ways to get rid of it using AutoNumber() function.

[caccio88]

sunindia Can you explain me better how is possible to use the AutoNumber() function in my script, please?

Thank u.

[sunny_talwar]

Here you go:

Table:

LOAD Date,

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

    YearMonth,

    [ITEM CODE],

    Stock,

    CMV

FROM

[Filtro CMV per stock 0 con CMV_v2.xlsx]

(ooxml, embedded labels, table is Foglio1);

AggregatedTable:

LOAD AutoNumber(YearMonth&'|'&[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 YearMonth,

  [ITEM CODE],

  Sum(CMV) as TotalCMV

Resident Table

Group By YearMonth, [ITEM CODE]

Order By [ITEM CODE], YearMonth desc;

Results are still be the same

Data Model

[caccio88]

Hi sunindia‌, in my real case I have to manage a lot of data.

Don't you think that using set analysis could optimize the visulization of my front end.

I tried this and seems that works in our example.

=Sum(Aggr(Sum({<Stock ={"<>0"} >} LAST3MonCMV), YearMonth, [ITEM CODE]))

What do you think about?

Thank you.

[sunny_talwar]

Set analysis surely improves performance. But I wasn't sure at what level do you want to perform the test. Sum(Sales) can be different from Sales if you have multiple rows for your dimensions. If you are going to have one sales amount for your dimension, then you can definitely go for it.

Another option is like this:

=Sum(Aggr(Sum({<Stock -={0}>} LAST3MonCMV), YearMonth, [ITEM CODE]))

But in general, I agree that set analysis is better performing compared to the if statement.

I guess in your real scenario, check if you get the same results or not. If you do, then go for set analysis

[caccio88]

sunindia‌

Thank u for your explanation. I think that for my target it would be the same to use "Stock" or sum(Stock), so I can use Set analysis.

Are there any difference between my solution =Sum(Aggr(Sum({<Stock ={"<>0"} >} LAST3MonCMV), YearMonth, [ITEM CODE])) and yours =Sum(Aggr(Sum({<Stock -={0}>} LAST3MonCMV), YearMonth, [ITEM CODE])).

Both consider only the case where Stock is different from zero, right?

One more thing, I tried to use Date (that's the field I got in my real scenario) to aggregate the fields in my script, but with this solution the front end doesn't work anymore...

Table:

LOAD Date,

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

    YearMonth,

    [ITEM CODE],

    Stock,

    CMV

FROM

(ooxml, embedded labels, table is Foglio1);

AggregatedTable:

LOAD AutoNumber(Date&'|'&[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 Date,

  [ITEM CODE],

  Sum(CMV) as TotalCMV

Resident Table

Group By Date,[ITEM CODE]

Order By [ITEM CODE], Date desc;

Can you help me, please?