Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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])
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.
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 you try with " instead of ' in the set analysis part?
Tried it. No change.
Can you a sample of the application?
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.
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.
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;
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))