Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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
Anonymous
Not applicable
Author

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?

Anonymous
Not applicable
Author

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

Anonymous
Not applicable
Author

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.

marcus_sommer

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

Anonymous
Not applicable
Author

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)

marcus_sommer

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

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?
Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
antoniotiman
Master III
Master III

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

Anonymous
Not applicable
Author

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