Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Customers, Partners & Luminaries only: You're invited to a Data Analytics Roadmap session. Read More
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator
Creator

test for comparability

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!

1 Reply
Highlighted
Specialist II
Specialist II

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