Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Save the Date: QlikWorld Online, June 24-25, 2020. Free global virtual event for data integration and data analytic gurus. Register Today
Highlighted
Partner
Partner

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
Highlighted
Valued Contributor II

Re: Above function in If expression on Dates

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
Highlighted
Valued Contributor II

Re: Above function in If expression on Dates

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

Highlighted
Partner
Partner

Re: Above function in If expression on Dates

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

Highlighted
Valued Contributor II

Re: Above function in If expression on Dates

In your original formula:

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

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

Highlighted
Partner
Partner

Re: Above function in If expression on Dates

 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? 

Highlighted
Partner
Partner

Re: Above function in If expression on Dates

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

thanks

Highlighted
Valued Contributor II

Re: Above function in If expression on Dates

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

Highlighted
Valued Contributor II

Re: Above function in If expression on Dates

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

Highlighted
Partner
Partner

Re: Above function in If expression on Dates

this is what i'm looking for

Highlighted
Valued Contributor II

Re: Above function in If expression on Dates

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?