Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I have sales data for the products at year level like below.
Year | Product | Sales |
---|---|---|
2000 | P1 | 30 |
2001 | P1 | 35 |
2002 | P1 | 54 |
2000 | P2 | 25 |
2001 | P2 | 43 |
2002 | P2 | 37 |
And in the frontend I have to display the previous year sales in a column like below
Year | Product | Sales | Prv Year Sales |
---|---|---|---|
2000 | P1 | 30 | - |
2001 | P1 | 35 | 30 |
2002 | P1 | 54 | 35 |
2000 | P2 | 25 | 54 |
2001 | P2 | 43 | 25 |
2002 | P2 | 37 | 43 |
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
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
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.
Try this
=sum({<YOUR_YEAR={$(=Only(YOUR_YEAR-1))}>} YOUR_SALESQUANTITY)
it should works
Hope it helps you
I already tried this but not working because my dimension itself 'Year'. Your expression will work if we select any year.
Thanks,
Manohar
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
You could use something like
Alt(Above(Sum(Sales)), $(=Sum({<Year={"=$(=Min(Year) - 1)"}>} Sales)))
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
Thank you Wassenar.. Actually P2 previous year sales in 2000 should be null only but still you given the solution for both.