Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Not applicable

Can't get one weeks missing data to show in Pivot Table

I have a weekly report that must only contain the data from one week.  The problem is that certain products are missing data for particular weeks and only occasionally have a value in them say once per month.  I need [Pairs per Package] to show up in the pivot table every single week without fail to simplify the maintenence of my reports (ie make them dummy proof).  I'm using three seperate dimensions and I've tried to make them show all values with no success.

I have tried using a set analysis below, but it makes no difference from just putting [Pairs per Package] in there.  It gives the set analysis 53 weeks worth of data to look at, but it won't add back the missing data.  What am I doing wrong?

MAX({< [Production Date] = {'>=$(=(v_LastWeekEnding))<=$(=(v_OneYearOutWeekEnding))'} >} [Pairs per Package])

16 Replies
Not applicable

Can't get one weeks missing data to show in Pivot Table

From a quick look, shouldn't your min condition take > instead of >=? Secondly does the number format for production date and v_LastWeekEnding, v_OneYearOutWeekEnding match?

Regards,

Kiran.

Not applicable

Can't get one weeks missing data to show in Pivot Table

I've tried changing the >= to > and that didn't change the output.

The formats are the same.  I use those two variables with the Production Date field to trigger automatic date selections on my reports.

Not applicable

Can't get one weeks missing data to show in Pivot Table

Can you try with " instead of ' in the set analysis part?

Not applicable

Can't get one weeks missing data to show in Pivot Table

Tried it.  No change.

Not applicable

Can't get one weeks missing data to show in Pivot Table

Can you a sample of the application?

Not applicable

Can't get one weeks missing data to show in Pivot Table

Would there be a way to binary reload the data so every week has an entry for [Pairs per Pack] based on the latest/most current actual entry for that particular field?  That would be a way to solve the problem I think.  I just don't quite know how to do that yet. LOL.

Not applicable

Can't get one weeks missing data to show in Pivot Table

I can't put a sample on here with actual data.  The IT department would club me like a baby seal if I did that.

Not applicable

Can't get one weeks missing data to show in Pivot Table

Yes,

Just do the concatenate for the table with zero values.

Temp:

Load Distinct Product Resident Sales;

Join(Temp)

Load Distinct [Production Date],0 as PairsperPackage Resident Sales;

Concatenate(Sales)

Load Product,  [Production Date], PairsperPackage [Pairs per Package]  Resident Temp;

DROP Table Temp;

Not applicable

Can't get one weeks missing data to show in Pivot Table

Just making sure to try everything, can you try this expression:

max(if([Production Date]>=v_LastWeekEnding and Production Date]<=v_OneYearOutWeekEnding,[Pairs per Package],0))