Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
c_pannese
Partner - Creator
Partner - Creator

Above function in If expression on Dates

HI all,

my table is:

qlik3.png

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

1 Solution

Accepted Solutions
lorenzoconforti
Specialist II
Specialist II

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

View solution in original post

17 Replies
lorenzoconforti
Specialist II
Specialist II

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))

c_pannese
Partner - Creator
Partner - Creator
Author

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.

articolodata scadenzadurata (gg)data fine scorte
A31/03/20201010/04/2020
B3/04/20203003/05/2020
B1/05/2020508/05/2020

 

Durata cames from other calculations

thanks

lorenzoconforti
Specialist II
Specialist II

In your original formula:

- which field is Column(1)? "Unita' di ma.."?

- why are you adding  "ceil(Column(3))" twice? 

c_pannese
Partner - Creator
Partner - Creator
Author

 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? 

c_pannese
Partner - Creator
Partner - Creator
Author

Maybe it's more correct calculate the cumulative of the field "Durata" for each article?

thanks

lorenzoconforti
Specialist II
Specialist II

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?]

lorenzoconforti
Specialist II
Specialist II

Can you share the QVF and an Excel file where you show exactly what you are looking for?

c_pannese
Partner - Creator
Partner - Creator
Author

this is what i'm looking for

lorenzoconforti
Specialist II
Specialist II

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?