Qlik Community

Qlik Sense App Development

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

Announcements
Make your voice heard! Participate in the 2020 Wisdom of Crowds® Survey. BEGIN SURVEY
Eaun
New Contributor III

Calculate Column

What I would like to do if possible is to get the sum total minus value and the sum total possitive value from a column in the table

the expression for the field is

=num((aggr(sum(TOTQTY),PART_NUMBER) -aggr(sum({<[HM000_WAREHOUSE]={1}>}[HM000_MOHTQ]),PART_NUMBER,[LOCATIONS-SYNONM],Planner))*[Standard Cost],'£#,##0.00;-£#,##0.00')

any help would be  greatly appreciated

 

1 Solution

Accepted Solutions
Highlighted
Partner
Partner

Re: Calculate Column

Hi Eaun,

The reason why this is not working, is because you are creating two arrays and subtracting them from each other. Try to put a sum around the aggrs:

=num(Sum(aggr(sum(TOTQTY),PART_NUMBER)) - Sum(aggr(sum({<[HM000_WAREHOUSE]={1}>}[HM000_MOHTQ]),PART_NUMBER,[LOCATIONS-SYNONM],Planner))*[Standard Cost],'£#,##0.00;-£#,##0.00')

Jordy

Climber 

Work smarter, not harder

View solution in original post

2 Replies
Highlighted
Partner
Partner

Re: Calculate Column

Hi Eaun,

The reason why this is not working, is because you are creating two arrays and subtracting them from each other. Try to put a sum around the aggrs:

=num(Sum(aggr(sum(TOTQTY),PART_NUMBER)) - Sum(aggr(sum({<[HM000_WAREHOUSE]={1}>}[HM000_MOHTQ]),PART_NUMBER,[LOCATIONS-SYNONM],Planner))*[Standard Cost],'£#,##0.00;-£#,##0.00')

Jordy

Climber 

Work smarter, not harder

View solution in original post

Highlighted
Eaun
New Contributor III

Re: Calculate Column

Thanks that worked