Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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).
I suggest using Interval Match to get this to work. Stock_Month will be interval matched to Manufacturing_month (Start of stock_month) and sale_month (End of stock_month).
I suggest using Interval Match to get this to work. Stock_Month will be interval matched to Manufacturing_month (Start of stock_month) and sale_month (End of stock_month).
Hi @amaaiia
The description is a little confusing, but it seems there is no master calendar. when it comes to the use of Island tables in general is not recommended.
Hope it helps.
It's a good idea but I have a lot of data. If manufacturing_month is Jan/2000 and sale_month is Dec/2010 (this can be possible), there would be lot of duplicated rows... but nice approach