Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello All,
I am facing issue in achieving a requirement in QV.
I am using a pivot table and I am dragging my Month Field on the top and taking rolling 12 month sum(Revenue) as expression.
I want to keep my rolling 12 month static and should show ‘0’ if a particular user doesn’t have any value corresponding to that product and Month.
Currently whenever I select any user it shows value to only those Months and products for which he have values.
When All Users selected:
Product | 2014-04 | 2014-05 | 2014-06 | 2014-07 | 2014-08 | 2014-09 | 2014-10 | 2014-11 | 2014-12 | 2015-01 | 2015-02 | 2015-03 | Total | |
A | 0.76 | 0.44 | 0.05 | 0.63 | 0.18 | 0.56 | 0.29 | 0.26 | 0.74 | 0.71 | 0.89 | 0.20 | 5.51 | |
B | 0.52 | 0.04 | 0.97 | 0.81 | 0.09 | 0.61 | 0.15 | 0.03 | 0.38 | 0.99 | 0.32 | 0.12 | 4.91 | |
C | 0.88 | 0.96 | 0.69 | 0.32 | 0.23 | 0.24 | 0.29 | 0.13 | 0.21 | 0.18 | 0.65 | 0.95 | 4.78 | |
D | 0.18 | 0.27 | 0.83 | 0.79 | 0.57 | 1.00 | 0.46 | 0.58 | 0.22 | 0.12 | 0.86 | 0.13 | 5.88 | |
E | 0.44 | 0.99 | 0.40 | 0.07 | 0.13 | 0.42 | 0.98 | 0.07 | 0.62 | 0.30 | 0.23 | 0.51 | 4.65 |
When a particular user selected:
Product | 2014-04 | 2014-09 | 2014-10 | 2014-11 | 2014-12 | 2015-03 | Total | |
A | 0.57 | 0.56 | 0.62 | 0.07 | 0.69 | 0.43 | 2.94 | |
B | 0.14 | 0.12 | 0.77 | 0.85 | 0.16 | 0.17 | 2.21 | |
C | 0.91 | 0.04 | 0.19 | 0.18 | 0.87 | 0.24 | 2.42 |
My Actual Requirement:
Product | 2014-04 | 2014-05 | 2014-06 | 2014-07 | 2014-08 | 2014-09 | 2014-10 | 2014-11 | 2014-12 | 2015-01 | 2015-02 | 2015-03 | MAT | |
A | 0.57 | 0 | 0 | 0 | 0 | 0.56 | 0.62 | 0.07 | 0.69 | 0 | 0 | 0.43 | 2.94 | |
B | 0.14 | 0 | 0 | 0 | 0 | 0.12 | 0.77 | 0.85 | 0.16 | 0 | 0 | 0.17 | 2.21 | |
C | 0.91 | 0 | 0 | 0 | 0 | 0.04 | 0.19 | 0.18 | 0.87 | 0 | 0 | 0.24 | 2.42 | |
D | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | |
E | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 |
Please let me know if any additional information is required.
Regards
KC
Hi Shaila,
I am still facing issue with this. Can you suggest your solution you used?
Regards
KC
Hi Peter,
Can you please help me with this? How can i create '0' in load script for the combinations?
Regards
KC
Since you're not sharing any (reduced) document, i'll give you a highly simplified example.
Imagine that you have a facts table called Facts with Month_Key, Product and Amount. Add a temporary key to your facts table, consisting of the combined values of Month_Key and Product in each row, for instance Prduct & '|' & Month_Key AS Temp_Key. The facts table should be resident before executing the following statements.
// Create Increment to facts
Increment:
NOCONCATENATE DISTINCT Product RESIDENT Facts;
LEFT JOIN (Increment) Month_Key RESIDENT Facts;
// Concatenate increment to Facts but only the missing rows
CONCATENATE (Facts)
LOAD Month_Key, Product, 0 AS Amount
RESIDENT Increment
WHERE Not Exists(Temp_Key, Product & '|' & Month_Key);
// Cleanup
DROP Table Increment
DROP Field Temp_Key;
Best,
Peter