ignoring some dimensions while respecting filters

Anonymous — Sat, 16 Nov 2024 05:09:06 GMT

Hello Community,

I have a 2-level problem with a table-diagram and the given dimensions.
I did already scan the forums and found two articles that seem related, but I haven't been able to extract the right information so far.

The mentioned articles are:
https://community.qlik.com/t5/New-to-QlikView/Ignore-dimension-in-set-analysis-while-maintaining-conditionals/td-p/1449250
https://community.qlik.com/t5/QlikView-Creating-Analytics/set-analysis-ignore-one-dimension-and-one-selection/td-p/272486

I want to set up a diagram-table that has three dimensions and several formulas. The dimensions are article-number, supplier-number and producer-number.
One of the formulas is intended to yield the date of the last offer. In practical use, there'll also most of the times be an "in-stock-filter" applied.

I found that this problem has to be divided in at least 2 parts.

(1) I have set up a simple table with just one dimension: article-number. It also contains 2 formulas
giving the date of the last offer and the document number of the respective document.
These are:
(a) Date(FirstSortedValue( {<Belegartgr={N02}>} DISTINCT Belegdatum, -Belegdatum))
(b) FirstSortedValue( {<Belegartgr={N02}>} DISTINCT BELbelegnummer, -Belegdatum)

The set-analysis {<Belegartgr={N02}>} restricts the results to offers only.
This simple table works nicely as long as no filters are applied. When I apply an "in-stock-filter" like "stock>0", results disappear.
This is logical, since stock-values are maintained on lot-level and not on article-level. Since offers do not have lot-numbers, there are no results left.
This hasn't posed a problem so far, since in a table-diagram I can always do sum(stock) to get the article-stock wherever needed. But for filters, this doesn't work.
How can I implement a filter that shows articles that have stock, regardless of lot-number?

(2) Now to the more complex table. This is the one with all three dimensions. This table is also designed to receive a "last offer date" column,
as in the simplified table above. I have tried to copy answers from the two mentioned discussions on this forum, but they didn't yield any results so far.

I have tried these:
(a) Date(FirstSortedValue( {<KeyHerstellerNr=,KeyLieferantNr=,Belegartgr={N02}>} DISTINCT Belegdatum, -Belegdatum))
(b) Date(FirstSortedValue( TOTAL <KeyHerstellerNr,KeyLieferantNr> {<Belegartgr={N02}>} DISTINCT Belegdatum, -Belegdatum))

Edit: also tried:

Neither gives any results, except (c) gives the most recent offer among all offers in the system in every row. That certainly wasn't intended either.

Once this works, it'll have to prove itself with a stock-filter applied (as mentioned in (1)).

I hope any of you can come up with an idea on how to implement this.
Thanks in advance!
Regards,
Eleusius.

P.S.: A sample is difficult, since it has to include dynamic data along with some fake core data (which I could come up with). I'll look into a sample regardless, but at first glance it seems a tough challenge.

Re: ignoring some dimensions while respecting filters

Anonymous — Fri, 23 Nov 2018 14:14:12 GMT

Additional information:

It might not be self-explanatory, but I expect the main reason (2) does not work is that offers are written for customers only. Hence given a dimension of supplier and producer, the system won't find any offers.

Thanks 🙂

Re: ignoring some dimensions while respecting filters

jensmunnichs — Fri, 23 Nov 2018 14:38:36 GMT

Any chance you could post some sample data to work with?

Re: ignoring some dimensions while respecting filters

sunny_talwar — Fri, 23 Nov 2018 14:45:23 GMT

Would you be able to post a sample to look at this?

Re: ignoring some dimensions while respecting filters

Anonymous — Fri, 23 Nov 2018 19:09:33 GMT

Hello and thanks for your attempt to help.

I have spent most of the afternoon constructing a simplified sample - however, the sample actually works as intended. No idea what to do next, as I cannot produce the problem in a somewhat small application.

Maybe the weekend gives me an idea.

Regards,

Eleusius.

topic Re: ignoring some dimensions while respecting filters in QlikView

ignoring some dimensions while respecting filters

Re: ignoring some dimensions while respecting filters

Re: ignoring some dimensions while respecting filters

Re: ignoring some dimensions while respecting filters

Re: ignoring some dimensions while respecting filters