Skip to main content
Announcements
Join us at Qlik Connect for 3 magical days of learning, networking,and inspiration! REGISTER TODAY and save!
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
1 Solution

Accepted Solutions
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

View solution in original post

22 Replies
Anonymous
Not applicable

Hi,

Uncheck Suppress zero values option in the presentation tab.

Regards,

Greeshma

jyothish8807
Master II
Master II
Author

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

Best Regards,
KC
Anonymous
Not applicable

Hi

Can you share your QVW?

jyothish8807
Master II
Master II
Author

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

Best Regards,
KC
jyothish8807
Master II
Master II
Author

Any Suggestions ? jagan

Best Regards,
KC
settu_periasamy
Master III
Master III

You need like the below, right?

Capture.JPG

check the attachment.

sarfaraz_sheikh
Contributor III
Contributor III

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

jyothish8807
Master II
Master II
Author

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

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

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.