Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Dear community,
I am looking to find the minimum date where stock, a field in my pivot table, different than 0.
How can I write that?
Thank you,
Abdallah
Found my mistake. Try:
Sum({<TRANSACTION_DATE = {'$(=Min({<STOCK -= {0}>} TOTAL TRANSACTION_DATE))'}>} TOTAL STOCK)
May be something like:
=Min({<stock-={0}>}date)
Hi again,
Thank you for the prompt answers.
Min({<STOCK -= {0}>} TRANSACTION_DATE) is giving me the minimum date. Now I want to use set analisys to calculate the
sum, the expression bellow is not working.
=Sum(TRANSACTION_DATE = {Min({<STOCK -= {0}>} TRANSACTION_DATE)}>}STOCK).
Can you please help me on that.
Best regards,
Abdallah
Hey,
Try
Sum({<TRANSACTION_DATE = {"=Min({<STOCK -= {0}>} TRANSACTION_DATE)"}>} STOCK)
Hi jensmunnichs,
I tryied the expression you proposed but the sum isn't returned. The minimum data is returned.
Enclosed the data:
as you can see, for the date 01/01/2018, 2 values of STOCK and min stock are reurned.
I want them to be added.
first value : 570.
second value : 10618.
Thank you,
Hi,
The dimensions I am using (showing) in my pivot table are:
1. TRANSACTION_DATE
2. STOCK
3. Product name
4. Product codes
and as expressions:
1. min date (where stock <> 0)
2. Min stock (for the found min date).
Thank you in advance,
Abdallah
Hi Jensmunnichs,
I tried the second option : The min date is returned but the minimum stock is not returned.
Now the expression of min STOCK, with 'TOTAL' added or not, is returning the sum of all STOCK value between the 2 dates:
vStartDate = Min(TRANSACTION_DATE)
vEndDate = Max(TRANSACTION_DATE).
Just to mention that I want the Sum of STOCKs of the Min(TRANSACTION_DATE) where STOCK is not null only. Because I might
find 2 or more values of the stock of same product (different sub-products codes).
In the bellow tables, the Min stock value should be 3393,000.
Start Date | = | 10/10/2018 |
End Date | = | 12/10/2018 |
LIB_PRODUIT | Min trans date | Min stock |
10/10/2018 | 9750 | |
PHOSPHATE | 10/10/2018 | 9750 |
LIB_PRODUIT | TRANSACTION_DATE | STOCK | Sum stock |
PHOSPHATE | 10/10/2018 | 114 | 114 |
PHOSPHATE | 10/10/2018 | 3279 | 3279 |
PHOSPHATE | 11/10/2018 | 973 | 973 |
PHOSPHATE | 11/10/2018 | 2296 | 2296 |
PHOSPHATE | 12/10/2018 | 792 | 792 |
PHOSPHATE | 12/10/2018 | 2296 | 2296 |
Total | Total | Total | 9750 |