topic Re: Calculate values (difficult) in App Development

Calculate values (difficult)

Anonymous — Fri, 02 Sep 2016 13:51:25 GMT

Hi there,

I have the following problem:

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

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

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

Re: Calculate values (difficult)

Anonymous — Tue, 06 Sep 2016 08:21:40 GMT

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?

Re: Calculate values (difficult)

Anonymous — Tue, 06 Sep 2016 09:00:37 GMT

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

Re: Calculate values (difficult)

Anonymous — Thu, 29 Sep 2016 06:11:58 GMT

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.

Re: Calculate values (difficult)

marcus_sommer — Thu, 29 Sep 2016 15:35:12 GMT

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

Re: Calculate values (difficult)

Anonymous — Fri, 30 Sep 2016 06:12:37 GMT

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)

Re: Calculate values (difficult)

marcus_sommer — Sat, 01 Oct 2016 10:08:03 GMT

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

Re: Calculate values (difficult)

vinieme12 — Sun, 02 Oct 2016 11:44:53 GMT

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?

Re: Calculate values (difficult)

antoniotiman — Sun, 02 Oct 2016 14:25:21 GMT

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

Re: Calculate values (difficult)

Anonymous — Thu, 06 Oct 2016 07:40:14 GMT

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_ID	PRODUCTGROUP_ID	STARTDATE	ADDED_AT_DATE	VALUE	RESULT
1	1	1	1	100	525
1	1	5	1	200	275
1	1	4	4	225	500
1	1	5	10	275	275
2	2	1	1	300	700
3	2	1	3	400	700

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

Re: Calculate values (difficult)

Anonymous — Thu, 06 Oct 2016 07:52:46 GMT

First I seperate the data into productgroups.

After this I take every article_id and sum up all values, whose added_at_date lies within the initial startdate and the startdate + 7 of the article_id-row.