Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
caccio88
Creator II
Creator II

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?

sunindiaswuehlgwassenaar

Thank u all.

Filiberto

1 Solution

Accepted Solutions
sunny_talwar

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


Capture.PNG

View solution in original post

18 Replies
swuehl
MVP
MVP

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
sunny_talwar

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


Capture.PNG

caccio88
Creator II
Creator II
Author

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
Creator II
Creator II
Author

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

Capture.PNG

Data Model

Capture.PNG

caccio88
Creator II
Creator II
Author

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
Creator II
Creator II
Author

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?