2 Questions: 1) Sum value at store level (ignore filter selection) & 2) return the total at store level before a certain day key in

[Gavin_FBu]

Hi,

I have a set of order by customer.  We require to develop a qilk sense dashboard for store people to select their customer and allow them to export a product order list for the customer that selected.  The raw data table look like:

Q1) We would like to create a formula to gives the total order at each store level for each product, like:

I have used aggr(sum(QTY), Branch), it looks fine if no filter selection however it won't work if I have filtered a customer.  Say if I selected 'Store A' and then 'John', the calculation will get filtered to John only and return me John's number only.
I would like the total for that product at store level ignore the filter on customer (only) and return 170 that is the total order on Store A for the product, like:

Q2) Second formula,  how do I put a date condition on the sum if I only want the sum of order at store level before a certain day  for that product at that store (will create a input variable for store to key in but let say 20/08/21 here).  Reminder this need to also consider the store people will filter the dashboard by branch and Customer. 

Say after I selected a Store A and John, it return: 

Many Thanks

[rubenmarin · Accepted solution]

Hi, I think the expression would be easier if you add an auxiliar table to data model, the key of this table would be a composite key by brach and product, an addition field may be the date to help with question 2 and the value field is the accumuladted sum, something like:

auxTable:
LOAD
  Branch &'_'& Product as Key,
  Date as DateB,
  Sum(QTY) as QTYAcum
Resident Datatable
Group By Branch, Product, Date;

With this table the expression "Sum(QTYAcum)" will give you the result for Q1

And "Sum({<DateB={"<=Date('$(vInputDate)')"}>}QTYAcum)" the result for Q2.

[Gavin_FBu]

Thanks for the suggestion.  I tried to break up my problem in a smaller piece in the beginning but apologies no giving the full picture at the first place as could be bit complex.  You're correct but we cannot create the QTYAcum as the number will become static.  

I think I will re-shape my question in a new post but your answer do fulfill the requirements I made in the post.

Cheers