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
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
Hi,
Uncheck Suppress zero values option in the presentation tab.
Regards,
Greeshma
Hi Greeshma,
Tried doing that, its not working. If i enable that it start showing value for all the Months and fails to show for rolling 12 months.
Regards
KC
Hi
Can you share your QVW?
Hi Greeshma,
I am using this in expression:
=sum({<Month_Key ={">=$(=date(Addmonths(Max (Month_Key),-11),'YYYY-MM')) <=$(=Date(Max(Month_Key),'YYYY-MM'))"}>}[Amount])
In Dimension:
1.Product
2.Month_Key
Sorry i wont be able to share the QVW.
Regards
KC
Any Suggestions ? jagan
You need like the below, right?
check the attachment.
Dear Settu,
can please share expression ....what you have used in above qvw as i am using personal addition and not able to see your solution ....
Sarfaraz
Hi settu,
I want to make selection on User level.Some users may may data for all the 12 months, but some may not.
So what i want to do is when i select a User who dosent have sales for particular month, the table should show '0' over there and should not get disappeared from the table.
Regards
KC
How about creating Zero-records for all periods and all users in your script? That may not look like the most advanced solution (in terms of expression complexity or intelligence) but it allows you to keep the user interface simple and transparent.