QlikView App Dev

[#1]

Now I have an urgent question. Please help me.

There is a data source, picture 1

Type, material 1, material 2, amount, cost

Use Qlikview production of the effect of the picture 2, the first dimension, the total amount, cost, gross profit is also very simple, but according to the material 2 gross margin >=60%, and the total 60%> gross margin =>50% count and total amount and accounting

How to write a script to calculate the formula?

I think I need to use aggr, but it's always wrong.

Please help me！

[#2]

Hi,

The aggr function can be tricky to use, please check out this post and let me know if it helps:

Use Aggregation Functions!

From what you have provided you will also need to incorporate some set Analysis into your formula. The post that I refer to often is:

Set Analysis: syntaxes, examples

Kind Regards

Arohan

[ahbzshk07]

Hello!

One possible way is

SCRIPT:

Load sum(GrossMargin) as TotalGrossMargin

Resident TableNameWhereGrossMarginFieldIsPresent;

DASHBOARD:

For the straight table chart, for the expression of material_level2_count;

=if(aggr(sum(GrossMargin),type,material_level1)>=0.6*TotalGrossMargin,aggr(Count(GrossMargin),type,material_level1),'-')

Likewise, for the other expressions...

Regards

[#4]

I am very sorry, in fact, the material dimension of the data source is more complex than the picture1, there are materials_level1, material_level2, and material_level3 , materials_level4..........., because of the data physical model, you provide the way is not very convenient, still thank you for your reply.I might end up thinking that way.

Thanks.

[padmanabhan_ram]

can you please attach the application with sample data.

Regards,

Padmanabhan

[#6]

Thank you very much for your help, this way very good, but there is a problem, when 'TYPE' are the same material_level2, the statistics of aggr have problems, for example: 'Imported'have '08A.03'  'local' also have "08A.03" material_level2, statistics will wrong, I try to add a column , "aggr ((sum (sales amount) -sum (cost of sales)) /sum (sales amount). MATERIEL_LEVEL2, TYPE)" join type dimension parameters, data are correct, But your formula adds the TYPE dimension, still not right!!should how to modify it???

Do you know what I mean?

[#7]

@Gilles Courtin‌

I have tried this way, not a complete success.

When the material data is 0, there will be a problem.

Look at the red mark on the Excel file. "08A.03" local

I think, this question about the missing value.

Regards

Rocco

[#8]

@Gilles Courtin

The problem is really complicated, and I find that this is a problem when TYPE-Imported and Local are in the same MATERIEL_LEVEL2-"08A.03". I think it should be the TYPE dimension is not completely separate, so I have a way of thinking, the report is divided into two parts, a "Imported", a "Local", but I still have problems, now look forward to your reply.

Regards

[#9]

Hi friend, please see the attachment and I reply with GILLES.

Regards

[#10]

Rocco,

What I did was wrong we can not use aggr function inside the {set expression} of our expression. The Set expression is evaluated once per chart and not by lline. Instead we can use if expression as you can see in the qvw attached.

Regards

Gilles