# Qlik Sense App Development

New Contributor III

## Calculate values (difficult)

Hi there,

I have the following problem:

I have a table containing article-data (sample-data):

1111100
1151200
2211300
3213400

Now what I need to do is to add a field to every row of this table.

Those fields are calculated by summing up all values of the table which have the same productgroup-id as in the current row and also need to have added_at_date in between the startdate and the startdate + 7.

Is there an efficient way to solve this problem?

I tried to create a new table called productgroup, which contains data like the productgroup_id, added_ad_date and dailyvalue (dailyvalue is an accumulation of all values for every productgroup_id and added_ad_date).

Doing so I am clueless about how to accumulate the data out of this table for every article_id in the article-table, since it would need to be accumulated for every row over a dynamic amount of rows in the productgroup-table.

I hope you can help me with ideas or techniques to solve this problem

10 Replies
New Contributor III

## Re: Calculate values (difficult)

I need to add a calculated field for every article_id.

Its value would be:

sum(value)

group by (productgroup_id)

where (productgroup_id = current_article_id)

and (added_at_date <= current_startdate + 7)

Is there no way to do it in Qlik Sense?

New Contributor III

## Re: Calculate values (difficult)

I can do the calculation in excel, using a simple sumifs-function.

New Contributor III

## Re: Calculate values (difficult)

Apparently this problem is too difficult to be solved with qlik-sense algorithms.

Therefor I had to reduce the problem and therefor the functionality.

Using a single date-value to calculate the productgroup value instead of a variable amount is possible.

MVP & Luminary

## Re: Calculate values (difficult)

I'm not sure that it couldn't be solved with qlik sense - but I don't really understand how your data look like and what do you want to do. What should be the expected output?

- Marcus

New Contributor III

## Re: Calculate values (difficult)

The output for this sample data would look like this:

 ARTICLE_ID PRODUCTGROUP_ID STARTDATE ADDED_AT_DATE VALUE RESULT 1 1 1 1 100 300 1 1 5 1 200 0 2 2 1 1 300 700 3 2 1 3 400 700

it is a group by productgroup_id, where for every article_id the value is summed up, if the added_at_date is in between the startdate and the startdate + 7.

As I mentioned this can be done if the startdate is fixed, but I don't see a way to do this with a variable startdate (depending on the article_id)

MVP & Luminary

## Re: Calculate values (difficult)

I think that you rather didn't need an aggregation load and more a check and summing of previous loaded records which could be done within a sorted load with: Peek() or Previous() ?

- Marcus

Esteemed Contributor II

## Re: Calculate values (difficult)

Need to understand this better

Can you denote how each of the RESULT's were calculated.

 ARTICLE_ID PRODUCTGROUP_ID STARTDATE ADDED_AT_DATE VALUE RESULT 1 1 1 1 100 300 How 300? 1 1 5 1 200 0 why Zero? 2 2 1 1 300 700 How 700? 3 2 1 3 400 700 how 700?
Honored Contributor III

## Re: Calculate values (difficult)

Hi Dennis

Temp:
PRODUCTGROUP_ID,
STARTDATE,
VALUE
FROM
"https://community.qlik.com/message/1117037"
(html, codepage is 1252, embedded labels, table is @1);

Left Join LOAD PRODUCTGROUP_ID,Sum(VALUE) as VALUE1
Resident Temp
Group By PRODUCTGROUP_ID;

Resident Temp;
DROP Field VALUE1;
Drop Table
Temp;

Regards,

Antonio

New Contributor III

## Re: Calculate values (difficult)

Hi Antonio,

I took a look into your solution and for the sample data it works very well.

Unfortunately the main problem does not seem to be represented by the given sample data.

This problem lies within the part, where you calculate the product-group values:

Left Join LOAD PRODUCTGROUP_ID,Sum(VALUE) as VALUE1

Resident Temp

Group By PRODUCTGROUP_ID;

It does not yet have the condition to only sum up data inside the given timespan.

I have tested it by adding two lines of sample data:

1111100525
1151200275
1144225500
11510275275
2211300700
3213400700

The value is, as I tried to explain, calculated for each and every article, not for the complete product group.

Hope this helps understanding the problem better, for the other ones helping as well.

-Dennis

Community Browser