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: 
musketeers
Creator
Creator

Row level aggrigation

Hi, I need to do row level aggrigation. The details are as below:

The data looks like below table: (Its a example and not real data)

PRODUCT QUARTER GOAL SALES SALE %
P1 Q2 0 0 -
P1 Q2 38 10 0.2632
P1 Q1 39 7 0.1795
P1 Q1 5 2 0.4

 

In above data I have calculated SALE % as Sale / Goal. Now the issue is I need to SUM up the SALE% for each quarter. Like for Q1 the SALE % is sum of 0.1795 + 0.4 = 0.5795 and for Q2 it is sum of 0.2632 + 0 = 0.2632

 

I am trying to use AGGR function like AGGR($(SALES) / $(GOAL), QUARTER).

But its workling in a different way. Its summing up the SALES and GOALS for that quarter (example Q1), and then dividing it. so AGGR shows 9 / 44 = 0.204545. But for me its not the requirement. I need to add the result of division.

Labels (2)
1 Solution

Accepted Solutions
canerkan
Partner - Creator III
Partner - Creator III

Hi Musketeers,

If that is the case, then you should calculate Sales and Goals in your load script and then use the suggested function.

View solution in original post

4 Replies
canerkan
Partner - Creator III
Partner - Creator III

Hi Musketeers,

maybe try AGGR(Sum(Sales / Goals), Quarter)

Let me know if it helped

Regards

Can

musketeers
Creator
Creator
Author

Hi Can, Many thaks for your suggestion. Sorry but I have not told that Sales and Goals are also calculated column. So I can not use SUM function as it is throw error of nested aggrigated expression.

canerkan
Partner - Creator III
Partner - Creator III

Hi Musketeers,

If that is the case, then you should calculate Sales and Goals in your load script and then use the suggested function.

musketeers
Creator
Creator
Author

Hi Can,

I have created calculation in backend Load script and it worked. Thanks for your suggestion.