Straight Tables - Filter Out Rows in a range

cistissimo — Wed, 31 Jan 2018 14:22:52 GMT

Hi everyone,

I need help from anyone smarter than me with QlikView.

I have a dashboard with many sheets.

In one of them I've a straight table filled with data concerning companies and time periods.

Data are loaded from a SQL view that I could change almost as I want.

There's one record for every combination of company + year + quarter, then I've many records for the same company, one for every quarter of every year.

I've created in the view another field called "quarter_order", containing sorting of the quarters present in the database. It's an incremental number without gaps.

Each record reports some values about points or quantities the company has scored in that period.

This is an example of the table:

Company	Year	Quarter	Quarter_Order	Points
Oscorp	2016	3	3	6
Oscorp	2016	4	4	4
Oscorp	2017	1	5	8
Oscorp	2017	2	6	4
Oscorp	2017	3	7	2
Oscorp	2017	4	8	10
Oscorp	2018	1	9	7
Umbrella Corporation	2016	3	3	5
Umbrella Corporation	2016	4	4	9
Umbrella Corporation	2017	1	5	3
Umbrella Corporation	2017	2	6	4
Umbrella Corporation	2017	3	7	6
Umbrella Corporation	2017	4	8	2
Umbrella Corporation	2018	1	9	8

Beside the table there are two listboxes, filtering the fields "year" and "quarter".

There's always just one selected value for both of them.

When i filter a quarter in the corresponding listbox, I want my table to show records for that quarter AND the 4 previous quarters, with resulting points and quantities.

Example:

I filter "year" = 2017, "quarter" = 4 in my listboxes.

I want my table to return results like this:

Company	Year	Quarter	Quarter_Order	Points
Oscorp	2016	4	4	4
Oscorp	2017	1	5	8
Oscorp	2017	2	6	4
Oscorp	2017	3	7	2
Oscorp	2017	4	8	10
Umbrella Corporation	2016	4	4	9
Umbrella Corporation	2017	1	5	3
Umbrella Corporation	2017	2	6	4
Umbrella Corporation	2017	3	7	6
Umbrella Corporation	2017	4	8	2

I know that would be easier to analyze data positioned on the same row for different quarters of a company, but users asked for this kind of solution.

I've tryed using set analysis but I've many expressions in my table, I'm not sure it'd work anyway.

I've tryed using a calculated dimension in the table but I failed.

How could I do it without using buttons and actions instead of my listboxes?

Can I "tell" my listbox to filter a "range of values" in a field instead of the field itself?

This is the first step, if anyone could find a way to do it, it would be great.

The second step would be to have this table working and another one in the same sheet showing ONLY the quarter before the quarter I've selected in the filter listobx. Using the same listbox to filter both of them, of course.

The two tables contain different expressions, the second one is not just a "reduced copy" of the first.

However, I would be really happy just making the first step working...

Thank you all!

Lorenzo

topic Straight Tables - Filter Out Rows in a range in QlikView

Straight Tables - Filter Out Rows in a range