topic Re: Unexpected filtering behaviour in QlikView

Unexpected filtering behaviour

Thu, 05 Nov 2015 14:40:39 GMT

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.

Re: Unexpected filtering behaviour

chaper — Thu, 05 Nov 2015 15:27:08 GMT

There no connection between periods or master calendar with your data.Looks like data model is incomplete.

Re: Unexpected filtering behaviour

chaper — Thu, 05 Nov 2015 15:32:36 GMT

Renamed Order date field to connect with your data. Reload this app and check whether it worked or not.use .

Re: Unexpected filtering behaviour

mvanlutterveld — Thu, 05 Nov 2015 15:35:09 GMT

Your filtering is exactly as you programmed it:

‘Cli’ is an expression:

=if((Month(OVR_ORD_DATA_ORDINE) = Month(Periodo) AND Year(OVR_ORD_DATA_ORDINE) = Year(Periodo)), CLIENTE_VEN_ORD_KEY)

If you look at CLIENTE_VEN_ORD_KEY and make a selection in that field (value 324), it returns 31/12/2014 and 30/01/2015 as OVR_ORD_DATA_ORDINE.

As Chaiperi stated: your Periods table is not connected to any other tables in your data model.

Re: Unexpected filtering behaviour

Thu, 05 Nov 2015 15:37:05 GMT

Yes, I know there are no connections, the file you're seeing is a deep simplification of original where I can't link periods to other entities, otherwise I'll get circular reference.

I tried also to duplicate master calendar tables (one for each entity), but then I have to ask to the final user to select just one of them and reflect selection to the others, but can't handle correctly.

I'm making connection between entities and unique master calendar using field expressions as you can see.

Is there any way to handle this behaviour?

Thanks.

Marco Pikiri

Re: Unexpected filtering behaviour

Thu, 05 Nov 2015 16:02:21 GMT

based on the data mode

there is no link between periods to mastercalendar and mastercalendar to any of the data

Re: Unexpected filtering behaviour

Thu, 05 Nov 2015 16:07:52 GMT

Ok, I understand, but is there any way to handle this behaviour "forcing" selection to not include outer values? Perhaps with set analysis? As a newbie I've started to read now about them, it seems a big world, but at now I need to synthesize to what I need now and satisfy my customer .

Sorry for pressing and thanks for your support!

Marco Pikiri

Re: Unexpected filtering behaviour

chaper — Thu, 05 Nov 2015 16:56:58 GMT

With out making any link in data I think it won't work in expression even if you write it.May be you should look into getting rid of Circular reference while making links in data model with different data sets.

Re: Unexpected filtering behaviour

mvanlutterveld — Fri, 06 Nov 2015 07:52:52 GMT

Sure, you can dive into set analysis. There are tons of posts on this forum about set analysis. Henric Cronström has some excellent readings about this topic. I'm sure you can go a long way with set analysis to reach your requirements.

But IMHO first start with mastering the skills of data modelling. Without a good data model you never get a good QlikView app.The data model is the engine of your app, set analysis isn't.

cheers,

Michiel

Re: Unexpected filtering behaviour

Colin-Albert — Fri, 06 Nov 2015 15:57:42 GMT

As Michiel says, you need to get your data model right. Set Analysis is a very powerful tool in QlikView but it is not a fix-all for an incorrect data model.

This post by hic‌ on Canonical dates may help in getting a correct calendar into your data model.

https://community.qlik.com/blogs/qlikviewdesignblog/2014/02/17/canonical-date

Re: Unexpected filtering behaviour

Fri, 06 Nov 2015 16:00:00 GMT

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

Re: Unexpected filtering behaviour

Mon, 09 Nov 2015 10:04:44 GMT

Hello good morning,

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

Thanks, regards.

Marco Pikiri

Re: Unexpected filtering behaviour

mvanlutterveld — Mon, 09 Nov 2015 12:48:58 GMT

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

Re: Unexpected filtering behaviour

Mon, 09 Nov 2015 14:13:55 GMT

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

Re: Unexpected filtering behaviour

mvanlutterveld — Tue, 10 Nov 2015 11:52:21 GMT

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

Re: Unexpected filtering behaviour

Fri, 13 Nov 2015 10:00:39 GMT

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

Re: Unexpected filtering behaviour

mvanlutterveld — Fri, 13 Nov 2015 12:19:23 GMT

Hi Marco,

Glad I could help!

Cheers,

Michiel