Qlik Sense - Displaying data by time series aggregated by a given time period

Question

I have data in the following format in Qlik Sense:

Date       Customer Flag
2016-10-01   A       1
2016-10-01   B       0
2016-10-02   A       1
2016-10-02   C       1
2016-10-03   A       1
2016-10-03   B       1
2016-10-03   C       1
2016-10-05   C       1
2016-10-10   A       0
2016-10-10   B       1
2016-10-11   C       0

I would like to display this data in a table in Qlik Sense in the following format:

#Week     Count Distinct Customer Where Flag is 1
39           2
40           3
41           3

Logic Behind: I need Week wise count of DISTINCT Customers who have Flag = 1 in the that Week including one previous Week. So, here Week Number 40 will display distinct count for Week 39 and 40. Similarly Week 41 will display distinct count for Week 40 and 41 and so on.

I would appreciate if anyone can suggest a Qlik Sense expression for the above?

Answer 1

As an alternative to Stefan's answer, Qlik Sense has some great features when it comes to dates. The below can be plugged into your script and will generate additional fields on the selector screen that can be used.

//AutoCalendar
[Calendar]: 
  DECLARE FIELD DEFINITION Tagged ('$date')
FIELDS
  Dual(Year($1), YearStart($1))                                         AS [Year] Tagged ('$axis', '$year')
  ,Dual('Q'&Num(Ceil(Num(Month($1))/3)),Num(Ceil(NUM(Month($1))/3),00)) AS [Quarter] Tagged ('$quarter')
  ,Dual(Year($1)&'-Q'&Num(Ceil(Num(Month($1))/3)),QuarterStart($1))     AS [YearQuarter] Tagged ('$axis', '$yearquarter')
  ,Month($1)                                                            AS [Month] Tagged ('$month')
  ,Dual(Year($1)&'-'&Month($1), monthstart($1))                         AS [YearMonth] Tagged ('$axis', '$yearmonth')
  ,Dual('W'&Num(Week($1),00), Num(Week($1),00))                         AS [Week] Tagged ('$weeknumber')
  ,Date(Floor($1))                                                      AS [Date] Tagged ('$date')
  /*User added date components*/
  ,Dual(Year($1), if(Year($1)=Year(today()),YearStart($1),null))        AS [ThisYear] Tagged ('$axis', '$thisyear')
  ,Dual(Year($1)&'-Q'&Num(Ceil(Num(Month($1))/3)), if(Year($1)=Year(today()),QuarterStart($1),null)) 
                                                                        AS [ThisYearQuarter] Tagged ('$axis', '$thisyearquarter')
  ,Dual(Year($1)&'-'&Month($1)
        , if(Year($1)=Year(today()), monthstart($1),null))              AS [ThisYearMonth] Tagged ('$axis', '$thisyearmonth')
  ,Dual(Year($1), if(Year($1)=(Year(today())-1),YearStart($1),null))    AS [LastYear] Tagged ('$axis', '$lastyear')
  ,Dual(Year($1)&'-Q'&Num(Ceil(Num(Month($1))/3))
        , if(Year($1)=(Year(today())-1),QuarterStart($1),null))         AS [LastYearQuarter] Tagged ('$axis', '$lastyearquarter')
  ,Dual(Year($1)&'-'&Month($1)
        , if(Year($1)=(Year(today())-1), monthstart($1),null))          AS [LastYearMonth] Tagged ('$axis', '$lastyearmonth')
  ,Dual(date(MonthStart($1),'MMM-YYYY')
        , if(Monthstart($1)=Monthstart(today()),Monthstart($1),null))   AS [ThisMonth] Tagged ('$axis', '$thismonth')
  ,Dual(date(MonthStart($1),'MMM-YYYY')
        , if(Monthstart($1)=Monthstart(addmonths(today(),-1)),Monthstart($1),null)) 
                                                                        AS [LastMonth] Tagged ('$axis', '$lastmonth')
  ,Dual(Year($1)&'-Q'&Num(Ceil(Num(Month($1))/3))
        ,if(QuarterStart($1)=QuarterStart(Today()),QuarterStart($1),null))  
                                                                        AS [ThisQuarter] Tagged ('$axis', '$thisquarter')
  ,Dual(Year($1)&'-Q'&Num(Ceil(Num(Month($1))/3))
        ,if(QuarterStart($1)=QuarterStart(addmonths(Today(),-3)),QuarterStart($1),null)) 
                                                                        AS [LastQuarter] Tagged ('$axis', '$lastquarter')
  ,Dual(date(MonthStart($1),'MMM-YYYY')
        ,if(QuarterStart($1)=QuarterStart(Today()),MonthStart($1),null)) AS [ThisQuarterMonth] Tagged ('$axis', '$thisquartermonths');

DERIVE FIELDS FROM FIELDS [CurrencyDate]
USING [Calendar] ;

This would use the same expression as Stefan.

= count( {< Flag = {1} >} distinct Customer)

The benefit of doing it this way

• fields are hidden in the dropdown so don't clutter field selecter
• multiple dates can be processed in the same stack, just add to the derive from fields delimiting with a ,

Answer 2

Seems to me that this should be solved in the script level instead trying to over complicate your expression/GUI (if possible at all)

The script below will create additional table Dates which will contain the Date --> Week link. The Week can be used as normal dimension.

RawData:
Load * Inline [
    Date      , Customer, Flag
    2016-10-01, A       , 1
    2016-10-01, B       , 0
    2016-10-02, A       , 1
    2016-10-02, C       , 1
    2016-10-03, A       , 1
    2016-10-03, B       , 1
    2016-10-03, C       , 1
    2016-10-05, C       , 1
    2016-10-10, A       , 0
    2016-10-10, B       , 1
    2016-10-11, C       , 0
];

// Generate week number from the existing dates
Dates_Temp:
Load 
    Date, 
    Week(Date)  as Week
Resident
    RawData
;   

Concatenate

// Generate previous week number from the existing dates
Load 
    Date, 
    Week(Date + 7)  as Week // Generate the previous week number
Resident
    RawData
;   

// The following code will remove week 42
// If week 38 need to be visible just ignore/delete 
// the script below --->

// Find the max week from the generated weeks
MaxWeek:
Load
    max(Week) as maxWeek
Resident
    Dates_Temp
;   

// Load the min week in vMaxnWeek variable
let vMaxWeek = peek('maxWeek');

// This table is not needed anymore
Drop Table MaxWeek;

NoConcatenate

// The new Dates table will contain all weeks apart from week 42
Dates:
Load
    *
Resident
    Dates_Temp
Where
    Week <> $(vMaxWeek)
;

Drop Table Dates_Temp;

let vMaxWeek = null();

After executing the script the data structure will look like this:

And Dates table will contain the following data:

As you can see for each date there are two weeks numbers accosted. (only week 39 is single record because week 38 is removed from the data. not sure if its needed)

So after this the expression is very simple:

= count( {< Flag = {1} >} distinct Customer)

And the result is:

P.S. the screenshots are from QlikView but the same load code and expression can be used in QS as well