Skip to main content
Announcements
Live today at 11 AM ET. Get your questions about Qlik Connect answered, or just listen in. SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
JustinDallas
Specialist III
Specialist III

Bar Chart: Prevent double counting from Detail table

Hello Folks,

TL;DR The sum from a detail table will be double counted if it maps to multiple rows in the Fact table, and if the calendar key on the Fact table splits the chosen time property (Week, Month, WeekStarting).  How is this generally dealt with.

 

So I've got data similar to dummy script below

 

Widgets:
LOAD *,
DayStart(Floor(Num("WidgetCreationDate"))) AS '%fact_calendar_key'
;
LOAD *,
WidgetNumber AS '%widget_number',
Date#(WidgetCreationDateString, 'YYYY-MM-DD') AS 'WidgetCreationDate'
;
LOAD * Inline
[
    'WidgetNumber','WidgetCreationDateString'
    1, '2020-03-01'
    2, '2020-03-15'
]
;
DROP FIELD WidgetCreationDateString
;

WidgetInvoiceJoin:
LOAD * Inline
[
    %widget_number, %invoice_number
    1, 1
    2, 1
]
;

Invoice:
LOAD *,
InvoiceNumber AS '%invoice_number'
;
LOAD * Inline
[
    'InvoiceNumber', 'InvoiceTotal'
    1, '300'
]
;

MasterCalendar:
LOAD 
 DayStart(Floor(Num(TempDate))) AS %fact_calendar_key,
 Dual(Date(WeekStart(TempDate), 'YYYY-MM-DD'), Num(WeekStart(TempDate))) AS WeekStarting
;
LOAD 
 DATE(mindate + IterNo()) AS TempDate,
 mindate,
 maxdate // Used in InYearToDate() above, but not kept 
WHILE mindate + IterNo() <= maxdate
;

//=== Get min/max dates from Field ===/
LOAD
 MIN(FieldValue('%fact_calendar_key', recno()))-1 as mindate,
 MAX(FieldValue('%fact_calendar_key', recno())) as maxdate
AUTOGENERATE FieldValueCount('%fact_calendar_key');

 

 

As you can see, I've got a single Invoice, and I have two widgets that were created in two different WeekStarting weeks.  Now, when I put the InvoiceTotal into a bar chart using the WidgetCreation date as the calendarkey in my Fact table, we see that the total appears in both weeks.

 

2020-03-26 11_26_23-Test2 - My new sheet _ Sheets - Qlik Sense.png

 

Of course, nothing is wrong with this data and it's logically consistent, but it confuses users and people who aren't familiar with what's going on.

 

Is there a way to somehow make the invoice total appear in the last widget creation date while maintaining the join?  Is there a better way to display this data from a UI perspective? 

 

Any help is greatly appreciated

Labels (2)
1 Reply
lorenzoconforti
Specialist II
Specialist II

Not actually sure this is possible. You could get the last WeekStarting by InvoiceNumber within a measure

The problem is that when you use WeekStarting as a dimension, you will correctly get InvoiceTotal for all the widgest numbers associated to that invoice