Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

dennis_o
New Contributor III

Calculate values (difficult)

Hi there,

I have the following problem:

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

ARTICLE_IDPRODUCTGROUP_IDSTARTDATEADDED_AT_DATEVALUE
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
dennis_o
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)

   and (added_at_date <= current_startdate + 7)

Is there no way to do it in Qlik Sense?

dennis_o
New Contributor III

Re: Calculate values (difficult)

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

dennis_o
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
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

dennis_o
New Contributor III

Re: Calculate values (difficult)

The output for this sample data would look like this:

ARTICLE_IDPRODUCTGROUP_IDSTARTDATEADDED_AT_DATEVALUERESULT
1111100300
11512000
2211300700
3213400700

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

vinieme12
Esteemed Contributor II

Re: Calculate values (difficult)

Need to understand this better

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

ARTICLE_IDPRODUCTGROUP_IDSTARTDATEADDED_AT_DATEVALUERESULT
1111100300 How 300?
11512000 why Zero?
2211300700 How 700?
3213400700 how 700?
antoniotiman
Honored Contributor III

Re: Calculate values (difficult)

Hi Dennis

Temp:
LOAD ARTICLE_ID,
PRODUCTGROUP_ID,
STARTDATE,
ADDED_AT_DATE,
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;

LOAD *,
If(ADDED_AT_DATE >= STARTDATE and ADDED_AT_DATE <= STARTDATE +7,VALUE1,0) as RESULT
Resident Temp;
DROP Field VALUE1;
Drop Table
Temp;

Regards,

Antonio

dennis_o
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:

ARTICLE_IDPRODUCTGROUP_IDSTARTDATEADDED_AT_DATEVALUERESULT
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