sum at a level in data

[Anonymous]

Hi - Have a problem, how can I sum at a level in QlikSense . For example I have the following data

Item , Data Set 1, Data Set 2, units

A, 10, 5, 3

B, 12, 2, 10

C, 4, 2, 2

I need to get a sum of   (Data Set 1 - Data Set 2)* Units

for item A 15 which is (10-5)*3

for item B 100 which is (12-2)*10

for item C 4 which is (4-2)*2

The KPI I am looking for is 119

[YoussefBelloum]

Hi

Simply apply for formula in the script, like this

kpi_119:

LOAD item, (Data_set1-Data_set2)* units as KPI;

LOAD * Inline [

item,Data_set1, Data_set2, units

A, 10, 5, 3

B, 12, 2, 10

C, 4, 2, 2

];

Please find attached

[jwjackso]

Try:  =Sum(Aggr(((DataSet1 - DataSet2) * units),Item))

[Anonymous]

I might of left 1 detail out ... the items repeat  so to get to a group by of each item I am using the following in a table with item ID and Item name as columns

item id , item name, expression : (sum(Set 1 )-sum(Set 2))*units

I Need these summed for all the items obviously I can't use the same expression because the weight of each item is different

[Anonymous]

sorry data load is not the issue, it issue is at a KPI level

[vishweshwarisun]

Hi,

try this

=sum((Data_set1-Data_set2)*units)

[kashaboinaraj]

hi

Javaid try  this

=sum((Data set1- Data set2)*units)

[YoussefBelloum]

on the app I've attached above, you have 119 on the table and on a textbox using Sum(KPI)..

what is missing ?

[Anonymous]

Thanks - I m attaching the actul query below

Dataset 1 = qty 867

Dataset 2 = qty 844

units = avg cb amt

since this is sales data i need to sum 844 and 867 at a product level.

If I have prodcut selected the following will work

=(sum(QTY_867)-Sum(QTY_844) )*AVG_CB_AMT

I need this to work for all products together and this following is not working

=sum((sum(QTY_867)-Sum(QTY_844) )*AVG_CB_AMT)