Skip to main content
Announcements
See why Qlik was named a Leader in the 2024 Gartner® Magic Quadrant™ for Data Integration Tools for the ninth year in a row: Get the report
cancel
Showing results for 
Search instead for 
Did you mean: 
winniethepooh
Contributor II
Contributor II

SUMPRODUCT

Hello,

I'm trying to get sumproduct result as in excel file for %Sales & GM2 Target at supracategory level. In this case, for mobile phones 7.4%.

Can you please help me? 

%Sales * GM2 Target doesn't work (result will be 8.9%).

winniethepooh_0-1685512359099.png

 

Labels (4)
1 Solution

Accepted Solutions
chrismarlow
Specialist II
Specialist II

Hi,

I can't open your QVW, but think you need to look at Total Mode, so from toy app below only difference between 3rd and 4th expression in screenshot from toy application below is on the 4th Total Mode is set to Sum of Rows - which gives same as SUMPRODUCT would in Excel.

Cheers,

Chris.

 

20230609_3.png

sales:
Load * Inline [
Order, Product
1, X
2, X
3, X
4, Y
5, Y
6, Z
7, Z
8, Z
9, Z
10, Z
];

target:
Load * Inline [
Product, Target
X, 5%
Y, 10%
Z, 20%
];

View solution in original post

3 Replies
chrismarlow
Specialist II
Specialist II

Hi,

I can't open your QVW, but think you need to look at Total Mode, so from toy app below only difference between 3rd and 4th expression in screenshot from toy application below is on the 4th Total Mode is set to Sum of Rows - which gives same as SUMPRODUCT would in Excel.

Cheers,

Chris.

 

20230609_3.png

sales:
Load * Inline [
Order, Product
1, X
2, X
3, X
4, Y
5, Y
6, Z
7, Z
8, Z
9, Z
10, Z
];

target:
Load * Inline [
Product, Target
X, 5%
Y, 10%
Z, 20%
];
sidhiq91
Specialist II
Specialist II

@winniethepooh  Could you please provide some sample excel file and how are you calculating % Sales

winniethepooh
Contributor II
Contributor II
Author

It worked! Thank you 🙏