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: 
Not applicable

Set Analysis - how to get previous year sales

Hi All,

I have sales data for the products at year level like below.

YearProductSales
2000P130
2001P135
2002P154
2000P225
2001P243
2002P237

And in the frontend I have to display the previous year sales in a column like below

YearProductSalesPrv Year Sales
2000P130-
2001P13530
2002P15435
2000P22554
2001P24325
2002P23743

I used Year and Product as dimensions but facing difficulty in writing set analysis for expression for Prv Year Sales. Can any body help me in writing set analysis for this.

P.S: We can do this by using peek() in the load script but I want to do it using set analysis.

Thanks in advance,

Manohar

1 Solution

Accepted Solutions
Gysbert_Wassenaar

You can't use set analysis here if you use year as dimension. The set is calculated per chart, not per row. You can use the above() function. I don't understand why the Prv Year Sales for product P2 in 2000 should be 54, but if you actually want it that way that's possible too using the total keyword in the above() function. See attached example


talk is cheap, supply exceeds demand

View solution in original post

7 Replies
Not applicable
Author

Pseudocode for script:

select s.*, f.Sales as 'Prv Year Sales'

from firstTable f

inner join secondTable s on f.year = s.year and f.product = s.product

Then just create a report straight table to visualize the data.

Anonymous
Not applicable
Author

Try this

=sum({<YOUR_YEAR={$(=Only(YOUR_YEAR-1))}>} YOUR_SALESQUANTITY)

it should works

Hope it helps you

Not applicable
Author

I already tried this but not working because my dimension itself 'Year'. Your expression will work if we select any year.

Thanks,

Manohar

Anonymous
Not applicable
Author

You could try by creating a new year field like (current_year - 1) as Previous_year in the first table

and then join the second table by the new field and article.

Perhaps you could use a mapping table too.

I hope I understood what you mean

CELAMBARASAN
Partner - Champion
Partner - Champion

You could use something like

Alt(Above(Sum(Sales)), $(=Sum({<Year={"=$(=Min(Year) - 1)"}>} Sales)))

Gysbert_Wassenaar

You can't use set analysis here if you use year as dimension. The set is calculated per chart, not per row. You can use the above() function. I don't understand why the Prv Year Sales for product P2 in 2000 should be 54, but if you actually want it that way that's possible too using the total keyword in the above() function. See attached example


talk is cheap, supply exceeds demand
Not applicable
Author

Thank you Wassenar.. Actually P2 previous year sales in 2000 should be null only but still you given the solution for both.