Hi!
I have the following data:
| ID | Type | manufacturing_month | sale_month |
| 1 | T1 | Nov/2019 | Feb/2020 |
| 2 | T1 | Jan/2020 | Mar/2020 |
| 3 | T2 | Jan/2020 | Jun/2020 |
| 4 | T3 | Sep/2019 | Jun/2020 |
| 5 | T2 | Sep/2019 | Dec/2019 |
| 6 | T3 | Oct/2019 | Jun/2020 |
And I want to create a Pivot Table like this:
Stock_month (right) --> Type(below) | Jul/2019 | Ago/2019 | Sep/2019 | Oct/2019 | Nov/2019 | Dec/2019 | Jan/2020 | Feb/2020 | Mar/2020 | Apr/2020 | May/2020 | Jun/2020 |
| Total | 0 | 0 | 2 | 3 | 4 | 3 | 5 | 4 | 3 | 3 | 3 | 0 |
| T1 | 0 | 0 | 0 | 0 | 1 | 1 | 2 | 1 | 0 | 0 | 0 | 0 |
| T2 | 0 | 0 | 1 | 1 | 1 | 0 | 1 | 1 | 1 | 1 | 1 | 0 |
| T3 | 0 | 0 | 1 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 0 |
It is a stock table, that is, for each stock_month (months that I select through filter pane) I want to check how many IDs have been manufactured at this stock_month or before (manufacturing_month <= stock_month), and have not been sold yet (sale_month >= stock_month).
I can't select manufacturing_month or sale_month as column for the pivot table because not all dates appear. So I have manually generated a new table with months from Jul/2019 to Jun/2020 with column name stock_month. I have added new filter pane with 'Stock' alternate state. The pivot table has Type as dimension, and aggr(only({Stock}stock_month),stock_month) as column. The measure is th following:
sum(aggr(if([sale_month]>= max({Stock}stock_month) and manufacturing_month<=max({Stock}stock_month),1,0), ID,stock_month))
It does what I want but only when I manually select a short amount of IDs, because with all the data loaded it gets stucked and the pivot table does not work (aggr function is not the best way for such amount of data).
I've tried with set analysis:
count({<ID={"manufacturing_month <= max({Stock} stock_month)"}+{"sale_month >= max({Stock} stock_month)"}>} ID)
but when max({Stock} stock_month) is inside set analysis it does not get columns stock_month, but the TOTAL stock_month (the maximum selected in the filter pane).
