Make display of certain data independent of selection?

[datanibbler]

Hi,

my current document is a turnover dashboard displaying variable turnover for a certain date, with a drilldown group to view the figures on different levels of detail.

Now I have incorporated fixed turnover (costs). Those, however, have no connection with the date which the user can select, they only change once in a while.

The effect is that as soon as the user selects a date, the fixed turnover is disregarded by QlikView and QlikView doesn't even display that level (variable vs. fixed) anymore since no sensible selection can be made on that level and jumps directly to the next level below that.

My question is:

Is there any way I can set QlikView to display the fixed costs regardless of a selection on the date field?

I will attach a jpg of my data_model to make things clearer: The table "Costs" contains my fix costs. It is linked by a synthetic key to one of the dimension tables (I can't help that, several fields just have to have the same name so that the data can be smoothly integrated). I might just concatenate it instead, but the table it is linked to consists of two joined tables, so getting the list of fields from that is more time-consuming than just going with the synthetic key.

The main table is the one labeled "Shipped_Packages" and the date which the user can select is "DepDay" from the table labeled "Trailer_History".

Thanks a lot!

Best regards,

DataNibbler

[er_mohit]

you have to remove the synchronization key for this

use join concept and alias tha name if there is two or more than  fields common in one table

[datanibbler]

Hi er.mohit,

okay, I thought that synthetic key might still spell trouble for me. The issue is, those fields absolutely HAVE to have the same name so they can be placed on the same level of the drilldown group by QlikView and so that my turnover formula works (it includes a filter on the field 'Partclass', so the fixed_costs table has to include that field so that fixed costs are not thrown out by that filter.

I can try to adapt this table so it has the same nr. of fields as the "View_Pkg_Items" and then concatenate it to that one?`Would that help matters?

Thanks a lot!

Best regards,

DataNibbler

P.S.: That is not so easy - there are two tables from which I take information about the individual items and I'd like to join them in the longer run - but I have to concatenate some lines to both of them before I do the join, right?

P.S.: Something seems to be wrong there - there is one field on which I always filter, which is in the masterdata table that I'd like to join. But I have to concatenate some lines to both the tables_to_be_joined and I guess the order in which these actions are executed is not right: When I filter on that field now, my fixed_cost items are excluded although the field on which the JOIN is performed has the same content both times.

[datanibbler]

Hi er.mohit,

it does not work - when I leave those two masterdata tables separate in the data_model (only linked), then concatenating to them both works fine and my fixed costs are displayed just like before - but still, as soon as the user selects a date, fixed costs are ignored and the corresp. level in my drilldown group is skipped.

Is there any way to fix that?

I guess the problem is that both the masterdata tables where I have concatenated my fixed costs are linked to the main table "Shipped_Packages" via Item_Nr., but "Shipped Packages" is linked to "Trailer_History" where my datefield is via "Tag_Nr", so the linkage probably does not span the three tables rgd. that added dataset.

Can I fix that?

Now the problem has split into two - joining the tables (but that one is rather just a "nice to have") and my problem with the date.

Best regards,

DataNibbler

[datanibbler]

Hi,

I got a tip from a consultant I am in contact with rgd. QlikView - he told me I could try using Set_analysis - but that is one of the issues I haven't yet dealt with and I don't know how it works.

Solving that (making the display of some data independent of a selection) might also help me solve another problem.

Can anybody help me here, please?

Thanks a lot!

Best regards,

DataNibbler

[datanibbler]

Hi,

such as I understand it, Set_analysis lets me define which dataset to use in specific calculations, independent of the selections a user has made. Correct?

That would be just what I need.

I have read up a bit and it seems that the filter {<1>} makes the display (or other kind of access) of the entire set of data independent of any user selection? That would be the right thing for me - to display fixed costs, I have introduced a new field where there is only any data for the records that come from the fixed_costs table - nine lines, everywhere else there is a numeric 0 in this field. So I could just display the sum of this field over the entire dataset in the document - that would have me adding 0 about a million times, but that doesn't matter.

Can anybody tell me the correct syntax for this? I cannot seem to get it right yet.

An issue might be that I have this (the sum of thise fixed_costs field) as part of another formula that calculates (variable) turnover as well - I cannot have two alternative formulas depending on the row (before any selection has been made), so ) I packed it all into one since adding 0 should not matter - but it might impede the working of set_analysis?

Thanks a lot!.

[datanibbler]

OK, I made it 😉 This is solved then.

[sujeetsingh]

Mingled up!!!

Nice how do you done that can you plz explain??

[datanibbler]

Hi Son of Sardar,

sure. The key is "Set_analysis". That, in short, lets you select, for certain formulas, the dataset on which to execute those, independent of any other user_selection.

If you formulate your formula like SUM({1<Date>} Cost) for example, the parameter 1 just after the first curly bracket tells QlikView to ignore a certain filter (in this instance, any operation performed on the field >Date<) and instead calculate the formula over the entire set of data in the document (read that in "QlikView 11 for Developers").

In my scenario, there is a calculation for the variable turnover based on several fields, all of which are empty for the fixed_costs and there is one field holding the amount of the fixed_costs which is empty in all the rest of the dataset - so adding one to the other produces the desired result.

HTH

Best regards,

DataNibbler