
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Above function in If expression on Dates
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
- « Previous Replies
-
- 1
- 2
- Next Replies »
Accepted Solutions


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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))

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
In your original formula:
- which field is Column(1)? "Unita' di ma.."?
- why are you adding "ceil(Column(3))" twice?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Maybe it's more correct calculate the cumulative of the field "Durata" for each article?
thanks


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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?]


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Can you share the QVF and an Excel file where you show exactly what you are looking for?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
this is what i'm looking for


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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?

- « Previous Replies
-
- 1
- 2
- Next Replies »