Unexpected filtering behaviour

[#1]

Hello,

I've an unexpected behaviour during my filter session.

Please see attached file where you can find a current selection for "Periodo" field starting from january to june 2015.

If you select "324" from "Cli" field I expect that Periodo selection limits to only january 2015, not also december 2014. I know there are items on data for "324" value that lie on december 2014, but I don't understand why Periodo selection include also december 2014. If I've previously selected jan-jun 2015 I don't want this other selection include outer items.

Thanks for support and best regards.

--

Marco Pikiri

Messaggio modificato da Marco Pikiri Data integration on file 2.

[#2]

I agree with you.

But please find attached new version of file (Qlik_Date_Filter2.qvw).

I've added data for another entity: its name is 'For' and it's parallel to 'Cli' entity. Each entity has its own date field to put in connection with master calendar: OVR_ORD_DATA_ORDINE for 'Cli' and OAR_ORD_DATA_ORDINE for 'For'.

What I achieved successfully at now is to leave master calendar unlinked with model and link it virtually just on final chart/select objects. So you have seen my formulas to sum OVR/OAR values depending if its corresponding date falls on master calendar period range.

Linking master calendar to model and getting rid of circular reference implies (as far as I know) to build another master calendar, so that I link the first one to 'Cli' entity and the second one to 'For': is it correct? Then have I to ask to my customer to select the same date range twice or I can avoid this working on triggers events?

Thanks.

--

Marco Pikiri

[#3]

Hello good morning,

did you have the opportunity to see my second attached qvw file?

Thanks, regards.

--

Marco Pikiri

[mvanlutterveld]

Hi Marco,

It’s very difficult for me to answer your questions as I don’t know the requirements of your app and I’m not familiar with your data source. I took a look at your script and it’s hard for me to understand the reasons why you setup your script in this way. I’m not saying it’s good or bad, just trying to make clear that it’s hard for me to answer your questions.

But nevertheless I give it a go

I assume your data is about Sales (Vendita), Purchases (Acquisto) and Orders (Commesse).

Normally the relation between those entities is quite simple. On date x you purchase something and sell stuff. Both (sales and purchases) have orders linked (sales order and purchase order).

Now let’s take a look at your data model. You want something with dates and in your data model there are two dates: OVR_ORD_DATA_ORDINE and OAR_ORD_DATA_ORDINE (sales date and purchase date). Both dates should be linked to the master calendar. Therefore create a link table with the following keys:

• A keyfield for sales date to link with the master calendar
• A keyfield for purchase date to link with the master calendar. This field should have the same name as the sales date key field (like _Date.KEY)
• A keyfield to link with your sales records
• A keyfield to link with your purchase records

This can be achieved by concatenating the OVR and OAR tables into one link table.

The Order table should link to both Sales and Purchase. As sales orders and purchase orders are different entities, the orders (commesse) should be created twice: one as sales order and one as purchase order. By rename fields you prevent QlikView from creating synthetic tables.

If you do it right you don't have to work with two calendars.

Cheers,

Michiel

[#5]

Hi Michiel,

think about "Commesse" as "Project".

My customer starts analyzing the model selecting a project or a collection of projects. This is the reason why this "Commesse" entity is the center of my star model.

Then he sets a range period on master calendar.

From this selection he wants to see simultaneously sales, purchases, customers and suppliers implied with, going deeper selecting specific customers/suppliers. In this phase we notice the unexpected behaviour on date filter.

With your suggestion I'll go to read twice project entity, ok, but the problem simply moves from a side to another: I'll have to manage two parallel "Commesse" rather than two parallel calendars.

Then which could be the best approach I should follow?

Thanks a lot, I appreciate your support, regards.

--

Marco Pikiri

[mvanlutterveld · Accepted solution]

Hi Marco,

I have to alter my previous post a bit as I didn’t understand the meaning of Commesse. Google Translate came up with Orders, but as you stated Project is more appropriate.

In the way you are describing the requirements of your app, you’re actually almost there in realising the data model. A good understanding of what the requirements are is half the work.

First the Commesse and date selections:

These should result in a set of sales and purchase records filtered by the selections made. Customers and suppliers should be associated automatically. This means that every sales record should link to a data value and a Commesse value. This must be done by using a central link table (synonym: fact table).

First read your sales records. Expand this table with a key field to the calendar (_Date.KEY) and Commesse (_Commesse.KEY). Create a temporary table with your purchase records and also add the _Date.Key and _Commesse.KEY. Concatenate the two tables.

Load your Commesse table and add the key field _Commesse.KEY. Load the master calendar and add the _Date.KEY key field.

Now both purchases and sales are linked to the calendar and commesse tables.

If needed you can create a Sales Detail table and Purchase Detail table to reduce the number of fields in the central link table.

For customers and suppliers I have to make an assumption and that is:

>> Customers link only to the sales table

>> Suppliers link only to the purchase table

So add a key field to the customer table in the sales table (_Customer.KEY) and do the same for the supplier in the purchase table (_Supplier.KEY).

Please take note that a selection in Supplier means that only purchase records are associated.

I've added an example of a data model to this post.

Cheers,

Michiel

[#7]

Michiel,

sorry for late, but finally this is the desired behaviour, I have to thank you a lot!

I've made it a fast try and I already see the difference with previous version.

Now I'm going to apply this concept to the final data model and hoping that other anomalies don't arise...

Thank you again, regards.

--

Marco Pikiri

[mvanlutterveld]

Hi Marco,

Glad I could help!

Cheers,

Michiel