Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
HI all,
my table is:
the test column is:
if(COlumn(1)>0,
If(Above(TOTAL [Cod.Articolo])=[Cod.Articolo],
above(total (today()-1+ceil(Column(3))))+ceil(Column(3)),
today()-1+ceil(Column(3))))
But it doesn't work. Any ideas?
Tahnk you all
Hi, I went over this. I don't think it's possible. For two reasons:
1 - Above doesn't work on dimensions, only on measures. There is a workaround for this: to create a measure with the function only()
2 - In the measure "Consumo medio" you have quite a complex expression. You have an aggregation with sum range nested on dimensions you don't have on your main table. The effect of this is that such expression returns a number of empty rows which are hidden by un-ticking "Include zero values" under Data Handling. Visually it is fine; the problem is that those hidden rows are still evaluated by the Above function; the result of this is that, even if you implement the workaround as per point 1, you are still stuck with these extra rows.
The only possible solution (but I'm not sure if it will work either) is to include in your loading script as much as possible of the calculations included in Consumo medio; I can see that most calculations and set expression elements are fixed so you should be able to bring them into your data structure. There will still be the challenge with the aggregation but I don't know how your data is structured and the workings of the various measures
Lorenzo
What outcome are you trying to achieve?
this calculation basically says: take today's date and remove one day, then add "Data fine scorte" from the row above and then add "Data fine scorte" from the current row. Unless I got the columns wrong, you are adding 3 times a date
above( total ( today() - 1 +ceil(Column(3) ) ) ) + ceil(Column(3))
i have two cases:
if the article appears only once or on the first row i need "yesterday+Durata (column 3)"
when article apeeras more than once i need " the above date +Durata" starting the second row for those article.
articolo | data scadenza | durata (gg) | data fine scorte |
A | 31/03/2020 | 10 | 10/04/2020 |
B | 3/04/2020 | 30 | 03/05/2020 |
B | 1/05/2020 | 5 | 08/05/2020 |
Durata cames from other calculations
thanks
In your original formula:
- which field is Column(1)? "Unita' di ma.."?
- why are you adding "ceil(Column(3))" twice?
which field is Column(1)? "Unita' di ma.."?
column(1) is "Giacenze Al"
"Unità di Magazzino" is a dimension
"Consumo Medio" is column(2)
Durata is column(3)
why are you adding "ceil(Column(3))" twice?
Because the first time i should calculate the starting date and the second time for adding new days. Isn't it?
- why are you adding "ceil(Column(3))" twice?
Maybe it's more correct calculate the cumulative of the field "Durata" for each article?
thanks
According to your previous post:
- 1st condition: "yesterday+Durata (column 3)" =today() - 1 + Column(3)
- 2nd condition: " the above date +Durata" =above(TOTAL [Data scadenza]) + Column(3) [in this case, do you need the above Column(3) or the one from the same row? Also, which one is the above date? Data Scadenza?]
Can you share the QVF and an Excel file where you show exactly what you are looking for?
this is what i'm looking for
This would go in Data Fine Scorte in your table:
=if(Column(1)>0,
If(Above(TOTAL [Cod.Articolo])=[Cod.Articolo],
today() - 1+above(total (ceil(Column(4)))),
today()-1+ceil(Column(3))))
Can you share the qvf?