Accumulation and ignoring filters

[adrianfer]

Here is my simplified Data model:

The FACT table is sort of a bridge between the other two tables. I need the tables to be filtered based on the Reference Date. For DIM_REFERRAL, it is equal to the Referral Date. For DIM_PAYMENT, it is equal to the Payment Date.

It works fine when selecting Reference Date.autoCalendar.FinYear. However, when I select Gender, obviously only the Referral table gets rendered. I've tried adding Gender= in the measure for the Payments chart to ignore selection on the Gender field, but it still is not rendering. Please help!

I've attached the app for your review, and also the sample data.

Thank you.

[HirisH_V7]

In Qlik Sense, {<Gender= >} (ignore selection) only works if the field exists in the data model association path from your current chart context.
If Gender is not associated with [Payment Amount] (e.g., lives in a different table or has missing links), then the modifier won’t take effect — the expression still filters by the reduced data model after selections.

[Vegar]

Instead of linking via the fact table, consider to link the two tables directly with each other.

Load [Payment Date] as %_date, * 

From Dim_Payment;

Load [Referral Date] as %_date, * 

From Dim_Referral;

[adrianfer]

I can't link with just the date. The fact table actually has an Owning Company field which I use for section access 

[stevedark]

Hi @adrianfer 

The set analysis of {<Gender=>} should remove that selection from the expression and mean the chart looks the same whether or not Gender is selected, regardless of the data model.

The fact it is messing up makes me think it is something to do with the accumulation. If you create a copy of the chart and remove accumulation does that still show the values even when gender is selected?

What I tend to do is build the accumulations by associating a new date dimension in the data model, so that each period associates with itself and all prior periods. I've written a blog post that explains the technique, and this may help in this case:

https://www.quickintelligence.co.uk/qlikview-accumulate-values/

Cheers,

Steve

[adrianfer]

Hi @stevedark ,

You're right, if I remove the accumulation, the chart renders. I've had a look at the blog post, and I am still trying to get my head around it. Do I associate a new date dimension on the just the month of the Reference Date?

Thanks

[stevedark]

Hi @adrianfer 

In the case of the chart you have there you just want to associate the month name to the accumulation dimension. So the table will look like this:

AccMonth,Payment Date.Month
January,January
February,January
February,February
March,January
March,February
March,March
April,January
etc.

This way each accumulation month links to itself and each previous month, allowing the accumulation to happen.

I'm not sure how you have arrived at the month dimension in your chart, as you have the full names, but if the months were derived from the Month function you could create the above table like this:

for iAc = 1 to 12   // Loop for each accumulation month
   for iPM = 1 to iAc   // Loop for each source month up to the accumulation month
      LOAD
         Month(MakeDate(2000, iAc, 1)) as AccMonth,
         Month(MakeDate(2000, iPM, 1)) as [Payment Date.Month]
      AUTOGENERATE(1);
   next
next

There are a number of other examples in the blog post as to how to build the accumulation dimension, should the above not work.

Hope that helps.

Steve