Qlik Community

Qlik Sense App Development

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

Announcements
See why Qlik is recognized as a Leader for the 10th year in a row – and discover how Qlik can help put your business in the lead. Get Report
Highlighted
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