How to use group by and summarize feature

Ask for Help
1

Hi ,
My table contains 5 columns
reference product / color / size / order date (day) / quantity

First I want to sum quantity based on
reference product / color / order date (week)

Then I want to determinate in fonction of a dynamic period the ordered quantity of
reference product / color

I imagine I should build a table which is composed by
reference product / color / order date.

Actually I have an another table composed by
reference product / color / description. When I use a relation between the 2 tables , I can sum but without managing the period.

Any Help is appreciated !

2

Have a look at the examples in the Query documentation.

1 Like
3

I have tested this.
But the problem with , I collect a list of order date which I can’t use.
The extracted format is compatible for calculation and I don’t have found a feature which can manage a list of items and in the same time translate date to number.
screenshot 1 : list of extracted dates by query

image
image292×530 8.02 KB

screenshot 2 : Math feature incompatible with the wanted column (Date)
image
image351×785 30 KB

4

You can’t use that column in a Math column because it’s an array.
For example, in the second row you have an array of 3 dates. How would you expect that to work inside the Math column - what would you expect the result to be?

5

The wanted format is year/weeknumber (ex : 202345)
Now I can build query between my tables, but the result is not what I wish.
I draw the diagram to explain the difference beetwen what I have obtain and what I wish
image

I imagine that
First I must create a new table with the keys :

  • weeknum
  • reference
  • color

Then with query feature and Order table , I can easily obtain the wanted report.
My question is it possible to obtain the report without I create this new table just using the existing tables (Product / Order / weeknum) ?