topic Re: Creating a new field dependent on several variables in QlikView

Creating a new field dependent on several variables

caccio88 — Wed, 06 Apr 2016 18:01:28 GMT

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

Re: Creating a new field dependent on several variables

swuehl — Wed, 06 Apr 2016 18:27:58 GMT

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

Re: Creating a new field dependent on several variables

sunny_talwar — Wed, 06 Apr 2016 19:03:42 GMT

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

Re: Creating a new field dependent on several variables

caccio88 — Thu, 07 Apr 2016 20:09:09 GMT

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.

Re: Creating a new field dependent on several variables

sunny_talwar — Thu, 07 Apr 2016 20:46:34 GMT

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

caccio88 — Fri, 08 Apr 2016 07:09:13 GMT

sunindia 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

sunny_talwar — Fri, 08 Apr 2016 09:01:54 GMT

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

Re: Creating a new field dependent on several variables

caccio88 — Sun, 10 Apr 2016 19:21:04 GMT

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.

Re: Creating a new field dependent on several variables

sunny_talwar — Sun, 10 Apr 2016 19:27:16 GMT

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

caccio88 — Mon, 11 Apr 2016 09:52:13 GMT

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?

Re: Creating a new field dependent on several variables

sunny_talwar — Mon, 11 Apr 2016 09:58:24 GMT

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.

Re: Creating a new field dependent on several variables

caccio88 — Mon, 11 Apr 2016 13:53:27 GMT

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.

Re: Creating a new field dependent on several variables

sunny_talwar — Mon, 11 Apr 2016 13:59:10 GMT

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

caccio88 — Thu, 05 May 2016 13:00:28 GMT

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

caccio88 — Thu, 05 May 2016 13:04:16 GMT

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

sunny_talwar — Thu, 05 May 2016 13:18:46 GMT

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

Re: Creating a new field dependent on several variables

caccio88 — Thu, 05 May 2016 13:25:26 GMT

Ok, nice!

Waiting for your opinion about my new development..

Re: Creating a new field dependent on several variables

sunny_talwar — Thu, 05 May 2016 13:26:22 GMT

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)

Re: Creating a new field dependent on several variables

sunny_talwar — Thu, 05 May 2016 13:29:30 GMT

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