Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
There no connection between periods or master calendar with your data.Looks like data model is incomplete.
Renamed Order date field to connect with your data. Reload this app and check whether it worked or not.use .
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.
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
based on the data mode
there is no link between periods to mastercalendar and mastercalendar to any of the data
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
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.
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
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