Calculation of non-zero items

[#1]

I have fact-table which contains purchase and sales of my items and list of items in separate table.  Let say I totally I have 10 items in my list.  I want to calculate rest of items on the particular day and how many items I have on  my store LOAD * INLINE [     No, Qty, Store     Item1, 5, St1     Item1, -5, St1     Item2, 4, St1     Item2, -2, St1     Item3, 3, St1 ]; Items: LOAD * INLINE [     No     Item1     Item2     Item3     Item4 ]; I want to have In result table : Store  No of Items ST1     2 That means that I have only Item2 and Item3 to the end of month on my Store. Item1 have been sold.  There are no Item. It looks that I should use aggr-function, but I can not right correct expression. Than you in advance

[anbu1984]

Sales:

LOAD * INLINE [    

No, Qty, Store    

Item1, 5, St1    

Item1, -5, St1    

Item2, 4, St1    

Item2, -2, St1    

Item3, 3, St1 ];

Items:

LOAD * INLINE [    

No    

Item1    

Item2    

Item3    

Item4 ];

NoConcatenate

Final:

Load Store as Str,Count(No) Where Qty > 0 Group by Store;

Load Store,No,Sum(Qty) As Qty Resident Sales Group by Store,No;

[#3]

Anbu, thank you for your reply, but I want to change my data model,  because I need calculate these data (and some other for selected period). My idea is calculate it the final application in pivot table (or other chats)/

[anbu1984 · Accepted solution]

Check this

[#5]

Thanks, it works