topic Re: Accumulating distinct values by week in a barchart in QlikView

Accumulating distinct values by week in a barchart

Wed, 01 Oct 2014 12:53:08 GMT

I need to create a bar chart that counts distinct customers by week cumulatvley.

Here is the expression I have used at present (which works ok when i plot each week individually

({<[Year] = {$(vMaxYear)}>}DISTINCT Customer)

If I select full accumulation it sums the totals from each week but doesn't remove duplicates in the customer number.

I think i probably need to use a rangesum but i'm not sure how to write this.

Any suggestions?

Re: Accumulating distinct values by week in a barchart

Gysbert_Wassenaar — Wed, 01 Oct 2014 13:31:23 GMT

Perhaps you can get the needed result with an AsOf table where every week is associated with itself and all its previous weeks. That way you should be able to get the accumulated distinct count. See this document for more information: Calculating rolling n-period totals, averages or other aggregations

Re: Accumulating distinct values by week in a barchart

Thu, 02 Oct 2014 13:43:13 GMT

Thanks for this, I've tried to add this to my script but it fails to load.

I have over 1,400,000 transactions to calculate against.- woudl this be causing the problem?

I tried the following

AsOfWeek:

load

FWeek as FWeek_AsOf,

FWeek + 1 - IterNo() as FWeek

Resident DEMAND

while IterNo() <= 52;

right join load FWeek Resident DEMAND;

AsOfWeek:

load 'Current' as Type,

FWeek as FWeek_AsOf,

FWeek as FWeek

Resident DEMAND;

Concatenate (AsOfWeek)

load 'Rolling 52' as Type,

FWeek as FWeek_AsOf,

FWeek + 1 - IterNo() as FWeek

Resident DEMAND

while IterNo() <= 52;

right join load FWeek Resident DEMAND;

Re: Accumulating distinct values by week in a barchart

Gysbert_Wassenaar — Thu, 02 Oct 2014 13:51:13 GMT

Two things spring to mind:

You probably want to use a load distinct when creating the AsOfWeek table to get the distinct weeks from the DEMAND table. That way you don't start with an AsOfWeeks table with 1,400,000 records.
Right joining the AsOfWeek table with the fact table isn't a good idea since it will explode your fact table.