Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Qliksters!
I have a question about the best way to test for comparability in terms of sales at specific locations.
So, I have a file loaded that has Store # and sales for each period this year and last. So, the file is organized as such:
Sales By Period Last Year Sales By Period This Year
Unit # P14 P21 P31 P41 P51 P61 P71 P15 P22 P32 P42 P52 P62 P72
1 x x x x x x x x x x x x x
2 x x x x x x x x x x x x x
3 x x x x x x x x x x x x x x
4 x x x x x x x x x x x x x
5 x x x x x x x x x x x x x x
6 x x x x x x x x x x x x x
etc
(Wow - it took a while to type that chart out)
So, I want to create a bar chart that shows percent change each period versus last year, but I ONLY want to include locations that had sales each period this year and last. In the above example, my chart would only include Unit #'s 3 & 5.
Can anyone suggest a formula I can use to accommodate this task?
Many thanks!
Hi,
as Unit in dimension, replace by :
if aggr(count (distinct Period), Unit) = count(total distinct Period),Unit)
(if there is no row when no sales)
and add eventually a test on year to take just this year and prior
regards