Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor II
Contributor II

Before() - calculation with empty/NULL fields

I have a question with the behaviour of the before() function.

I have a table with customers, articles and then I am showing the total sold. In order to show the change in sales I use the before() function to calculate the evolution over the years:

(Sum(SalesInvoiceLine.LineAmount)-
    before(Sum(SalesInvoiceLine.LineAmount)))
    /
    before(Sum(SalesInvoiceLine.LineAmount))
)

My Problem is with an article which in 2017 has not been sold, but in 2016 it has been - the variation is shown as -0,6 which is not true - well, it is true if I wanted to know the change to the last time I sold this item. But I'd like to show here the change to 2017. How can I acomplish this? Sample data attached.

2018-11-27_090746.jpg

 

Labels (2)
2 Solutions

Accepted Solutions
Highlighted

I suggest you to use The As Of Table instead of trying to fix this on the front end. Not to say that it is not possible, but it will be a messy

View solution in original post

Highlighted
Contributor II
Contributor II

Thank you for your fast response. The solution you suggested works, but brings me to the result that the first column of the Variance is Empty, which is the correct behaviour of the before() function.

I even found another solution, but as you said, a messy one and a less dynamic one. Instead of using the before() function I removed the Year Dimension and recreated all columns by using a set analysis.

This works as expected, also showing a variance in the first column but is less dynamic as every column has to be "hardcoded" using set analysis.

View solution in original post

5 Replies
Highlighted

I suggest you to use The As Of Table instead of trying to fix this on the front end. Not to say that it is not possible, but it will be a messy

View solution in original post

Highlighted
Contributor II
Contributor II

Thank you for your fast response. The solution you suggested works, but brings me to the result that the first column of the Variance is Empty, which is the correct behaviour of the before() function.

I even found another solution, but as you said, a messy one and a less dynamic one. Instead of using the before() function I removed the Year Dimension and recreated all columns by using a set analysis.

This works as expected, also showing a variance in the first column but is less dynamic as every column has to be "hardcoded" using set analysis.

View solution in original post

Highlighted

But did you get a chance to look at The As Of Table link that I shared?
Highlighted
Contributor II
Contributor II

Yes, but this leads me to the result where the first variation column is empty (which is the correct behaviour for the before function). Another, not mentioned before, feature of my tabe was that this column has to be filled with values.

In this screenshot my table above vs. the solution with the AsOfYear as Dimension (the lines are article codes).

2018-11-28_105905.jpg

Highlighted

If implemented correctly, I don't think you should be seeing this problem. Difficult to say without seeing what you had and what you did.