Skip to main content
Announcements
NEW: Seamless Public Data Sharing with Qlik's New Anonymous Access Capability: TELL ME MORE!
cancel
Showing results for 
Search instead for 
Did you mean: 
jyothish8807
Master II
Master II

How to create static dimension in pivot table (QV)

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-042014-052014-062014-072014-082014-092014-102014-112014-122015-012015-022015-03Total
A 0.760.440.050.630.180.560.290.260.740.710.890.205.51
B 0.520.040.970.810.090.610.150.030.380.990.320.124.91
C 0.880.960.690.320.230.240.290.130.210.180.650.954.78
D 0.180.270.830.790.571.000.460.580.220.120.860.135.88
E 0.440.990.400.070.130.420.980.070.620.300.230.514.65

When a particular user selected:

   

Product 2014-042014-092014-102014-112014-122015-03Total
A 0.570.560.620.070.690.432.94
B 0.140.120.770.850.160.172.21
C 0.910.040.190.180.870.242.42

My Actual Requirement:

   

Product 2014-042014-052014-062014-072014-082014-092014-102014-112014-122015-012015-022015-03MAT
A 0.5700000.560.620.070.69000.432.94
B 0.1400000.120.770.850.16000.172.21
C 0.9100000.040.190.180.87000.242.42
D 0.000.000.000.000.000.000.000.000.000.000.000.000.00
E 0.000.000.000.000.000.000.000.000.000.000.000.000.00

Please let me know if any additional information is required.

Regards

KC

Best Regards,
KC
22 Replies
jyothish8807
Master II
Master II
Author

Hi Shaila,

I am still facing issue with this. Can you suggest your solution you used?

Regards

KC

Best Regards,
KC
jyothish8807
Master II
Master II
Author

Hi Peter,

Can you please help me with this? How can i create '0' in load script for the combinations?

Regards

KC

Best Regards,
KC
Peter_Cammaert
Partner - Champion III
Partner - Champion III

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