Subtraction in Script part

MMK — Mon, 28 Apr 2025 12:46:02 GMT

Hi All,

Thanks in advance for your support.

I am having a table with EMP and their sales. Under employee the given data is like this

EMP Sales

Group1 1000

E1 150

E2 200

E3 100

E4 250

Uder Group1 - total 7 Emp are there. we need to show sales. But

Last 3 Employee sales details are missed we need to show last 3 employees sales as 'Others'

Others Sales = Group1 - (E1+E2+E3+E4)

I want to show in Qlik EMP Sales Group1 1000 E1 150 E2 200 E3 100 E4 250 Other 300

300 = 1000-(150+200+100+250). I want achieve this through backend only.

Re: Subtraction in Script part

JandreKillianRIC — Tue, 29 Apr 2025 07:30:59 GMT

Hi @MMK

This might be a bit of a long way around, but it works. I also added more groups as this might not always be only 1 group in the data... I wasn't sure.

Here is the resulting Table

Here is the script

//////////////////////////////////////////////////////////////////////////////// // Load inline with more than one group as this might be what is in the data and not // just in the sample provided //////////////////////////////////////////////////////////////////////////////// TmpSales: Load *, RecNo() as SortingTmp; // Adding this for the sorting in the front end Load * Inline [ EMP,Sales Group1,1000 E1,150 E2,200 E3,100 E4,250 E5, E6, E7, Group2,1420 E1,150 E2,200 E3,100 E4,250 E5, Group3,1970 E1,150 E2,200 E3,100 E4,250 E5,500 E6,600 E7, E8, E9, ]; //////////////////////////////////////////////////////////////////////////////// // Define group level per individual line item //////////////////////////////////////////////////////////////////////////////// TmpSales2: Load SortingTmp, EMP, IF(Sales = 0 or Len(Trim(Sales)) = 0, 0, Sales) as Sales, // Cleaning up the Sales for later IF(WildMatch(EMP, 'Group*'), EMP, Peek('GroupName')) as GroupName, // Getting the Group that the Sales belong to IF(WildMatch(EMP, 'Group*'), Sales, Peek('GroupSales')) as GroupSales // Adding the Group Totals as this is to be used later Resident TmpSales; Drop Table TmpSales; //////////////////////////////////////////////////////////////////////////////// // Get totals per group (Excl the group values) //////////////////////////////////////////////////////////////////////////////// GroupTotalsLineItems: Left Join (TmpSales2) Load GroupName, Sum(Sales) as TotalSales Resident TmpSales2 Where Not WildMatch(EMP, 'Group*') // Excl the Group Value Group By GroupName; //////////////////////////////////////////////////////////////////////////////// // Calculate the Others (Sales that are 0) //////////////////////////////////////////////////////////////////////////////// TmpSales3: Load IF(Sales = 0, Peek('Sorting')+1, SortingTmp) as Sorting, // Fixing the sorting as we are grouping the Sales EMP where sales = 0 EMP, IF(Sales = 0, 'Others', EMP) as ReportingEMP, GroupName, TotalSales, GroupSales, Sales, IF(Sales = 0 and Peek('ReportingEMP') <> 'Others', GroupSales - TotalSales, Sales) as ReportingSales Resident TmpSales2 Order By SortingTmp; Drop Table TmpSales2; //////////////////////////////////////////////////////////////////////////////// // Clean up table (Remove all 0's as this is catered for) //////////////////////////////////////////////////////////////////////////////// Sales: NoConcatenate Load * Resident TmpSales3 Where ReportingSales <> 0; Drop Table TmpSales3;

topic Re: Subtraction in Script part in App Development

Subtraction in Script part

Re: Subtraction in Script part