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

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

Wed, 19 Oct 2011 14:38:30 GMT

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])

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

Wed, 19 Oct 2011 14:45:23 GMT

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.

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

Wed, 19 Oct 2011 15:27:11 GMT

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.

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

Wed, 19 Oct 2011 15:29:47 GMT

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

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

Wed, 19 Oct 2011 15:35:08 GMT

Tried it. No change.

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

Wed, 19 Oct 2011 15:36:24 GMT

Can you a sample of the application?

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

Wed, 19 Oct 2011 15:39:09 GMT

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.

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

Wed, 19 Oct 2011 15:42:32 GMT

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.

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

Wed, 19 Oct 2011 15:58:36 GMT

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;

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

Wed, 19 Oct 2011 16:37:55 GMT

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))

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

Wed, 19 Oct 2011 18:30:01 GMT

the

max(if([Production Date]>=v_LastWeekEnding and Production Date]<=v_OneYearOutWeekEnding,[Pairs per Package],0)) expression didn't work.

The binary fix you posted shows red squiggly lines under the second to last line that says:

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

I underlined the part with the red squiggly lines that indicate a syntax error. Is that section correct and the error check is a false alarm? I know that happens from time to time, but I'm unsure in this case.

Regardless of outcome Kiran, I want to thank you for taking the time to help me.

Scott

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

Wed, 19 Oct 2011 18:32:09 GMT

I am sorry it should be:

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

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

Wed, 19 Oct 2011 18:50:29 GMT

Reading your first post "it makes no difference", the data may me such that max value of pairs per package is during last week. I recommened we try with count function, which must tell us how many rows are considered with and without the set analysis. If there is a difference there is no question of anything being wrong with the set analysis.

Singing off for the day, will sure solve this by tomorrow.

Regards,

Kiran.

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

Wed, 19 Oct 2011 20:11:15 GMT

The Max value happens randomly in the future as well as the past. I think the one particular indicator of the error that I use to check if there has been a change has the max value within a month or so. The problem is that there is no record of the product at all when I'm pulling the data for the current week ending date, so the pivot table doesn't show it or try to find the max value that it has in the Pairs Per Package field. It just ignores it entirely as if it never existed.

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

Thu, 20 Oct 2011 02:12:44 GMT

Thats how qlikview behaves. What is expected when the product has no data during last week? Should the value be zero or show its most recent transaction or anything else?

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

Thu, 20 Oct 2011 13:33:09 GMT

I want it to show the maximum value for the field regardless of date (not zero) and I need it to show that same value every week even if there are no other data fields (expressions) to display.

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

Thu, 20 Oct 2011 15:29:41 GMT

Can you give a sample data set masking the values etc., and expected output? I am not able to envision your requirement exactly.

Regards,

Kiran.