18 Replies Latest reply: May 5, 2016 9:29 AM by Sunny Talwar

# Creating a new field dependent on several variables

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?

Thank u all.

Filiberto

• ###### Re: Creating a new field dependent on several variables

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
ABC2015032822011171117
ABC20150443662013561356
ABC201505201018311831
ABC201506048700
ABC201507085900
ABC20150831048513341334
ABC201509388667667667
ABC20151038866700
XYZ2015032822011301130
XYZ20150443662013691369
XYZ20150502300
XYZ201506048700
XYZ20150751085918191819
XYZ20150831048513341334
XYZ201509388667667667
XYZ20151038866700
• ###### Re: Creating a new field dependent on several variables

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

Table:

YearMonth,

[ITEM CODE],

Stock,

CMV

FROM

[Filtro CMV per stock 0 con CMV_v2.xlsx]

(ooxml, embedded labels, table is Foglio1);

AggregatedTable:

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];

[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]))

• ###### Re: Creating a new field dependent on several variables

Hi stalwar1,

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.

• ###### Re: Creating a new field dependent on several variables

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

• ###### Re: Creating a new field dependent on several variables

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

Thank u.

• ###### Re: Creating a new field dependent on several variables

Here you go:

Table:

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];

[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

• ###### Re: Creating a new field dependent on several variables

Hi stalwar1, 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]))

Thank you.

• ###### Re: Creating a new field dependent on several variables

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

• ###### Re: Creating a new field dependent on several variables

stalwar1

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:

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

YearMonth,

[ITEM CODE],

Stock,

CMV

FROM

[C:\Users\fcacciari\Desktop\KPMG\00_Progetti\Unieuro\02_Working Capital\QlikView\Ricevuti\Mazzotti_Check\Nuove Analisi\DII con CMV=0\Filtro CMV per stock 0 con CMV_v2.xlsx]

(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];

[ITEM CODE],

Sum(CMV) as TotalCMV

Resident Table

Group By Date,[ITEM CODE]

Order By [ITEM CODE], Date desc;

• ###### Re: Creating a new field dependent on several variables

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.

• ###### Re: Creating a new field dependent on several variables

stalwar1

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:

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

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

[ITEM CODE],

Stock,

CMV

FROM

[C:\Users\fcacciari\Desktop\KPMG\00_Progetti\Unieuro\02_Working Capital\QlikView\Ricevuti\Mazzotti_Check\Nuove Analisi\DII con CMV=0\Filtro CMV per stock 0 con CMV_v2.xlsx]

(ooxml, embedded labels, table is Foglio1);

AggregatedTable:

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];

[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.

• ###### Re: Creating a new field dependent on several variables

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

• ###### Re: Creating a new field dependent on several variables

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

• ###### Re: Creating a new field dependent on several variables

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

I see a strange file extension...

• ###### Re: Creating a new field dependent on several variables

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

• ###### Re: Creating a new field dependent on several variables

Ok, nice!