Hi Lydia,
The user will select a collectionCode from the collection register in a slicer.
The collection register will now be filtered to only show modelColour for the chosen collection in another slicer.
The user now chooses one of the modelColour available.
The collection register will have a relationship with the article register through the field collection[modelColour] and articles[modelColour]. I need this register to be able to group on sizes (articles[size]) and not only model and colour in my matrix visualization.
The article register will have relationsships that looks like below:
articles[articleNo] -> stock[articleNo]
I need this to be able to include the stock quantities in my calculation
articles[articleNo] -> allocation[articleNo]
I need this to be able to include order reservations and new purchases that are coming home for each available period.
Finally we have the warehouse register that will have the following relationsships:
warehouse[warehouseCode] -> stock[warehouseCode]
warehouse[warehouseCode] -> allocation[warehouseCode]
This will be used in a slicer to filter for which warehouse the calculation should be done.
I can now filter the stock and allocation register down on the correct article numbers (modelColour down on sizes) and warehouse.
To determine the different periods and its startdates I would need to do a calculation that finds all the rows in the allocation register for the field [type] that has a value of P. These rows are all the incoming purchases and will determine how many periods that exists and which startDate they have. The first period will always be "Today". If there aren't any rows with P there will only be one period that starts Today. If there are rows with P the row with the lowest periodStartDate will be period 2, the row with P with the second lowest periodStartDate will be Period 3 and so on.
This is were my problem starts as I don't know how to calculate this. It would need to be some kind of table as I need the periodStartDates as a grouping in my matrix visual for each size.
Then we need to calculate the available stock for each size for those determined periods.
Period 1 (periodStartDate = Today) = Sum of stock[qty] + sum of allocation[qty] (filtered on allocation[period] = 1). Order reservations will have negative quantities so if we have 20 pcs on stock and 10 pcs on reservations the result should be 20+(-10) = 10 pcs.
Period 2 (periodStartDate = the date for the row where allocation[type] is P and has the lowest [periodStartDate], for example 2018-02-01) = Sum of stock[qty] + sum of allocation[qty] (filtered on allocation[period] <=2).
For example we purchased 50 pcs and have 20 pcs on order in that period. The result would be 20+(-10+50-20) = 40 pcs
Period 3 (periodStartDate = the date for the row where allocation[type] is P and has the second lowest [periodStartDate], for example 2018-04-01) = Sum of stock[qty] + sum of allocation[qty] (filtered on allocation[period] <=3).
For example we purchased 100 pcs and have 50 pcs on order in that period. The result would be 20+(-10+50-20+100-50) = 90 pcs.
and so on.
The matrix visual would then look like below if my example above was for the size S.
Size
PeriodStartDate S M L XL XXL XXXL
2017-10-31 10
2018-02-01 40
2018-04-01 90
How can I create a calculation for the availableQty that will be grouped in the correct way in my matrix visual depending on both articles[sizes] (columns) and "newTable"[periodStartDates] (rows) and show the correct results?
To answer your questions:
1. What fields do you use to create relationship among the above tables?
Please see above.
3. How do you get 2018-04-01 PeriodStartDate in the Matrix visual? I don't see this value in your allocation table.
This was just a fictive example so the numbers aren't correct as our allocation register have hundreds of tousands of rows.
3. How do you return the numerical values in the Matrix visual based on the above sample data?
This is what I need help with as I don't know how to make the correct calculation. Please see above what the correct result should look like.
Regards,
Patrik
