How to create static dimension in pivot table (QV)

[jyothish8807]

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

[settu_periasamy]

Hi Sarfaraz,

I don't use any special expression. Just i did the below,

In the Dimension tab, I checked, Show All value, and In the Presentation tab, Unchecked the Suppress zero values option.

[jyothish8807]

Peter we have tried that also, but the issue is that the app will be updated on a daily basis and we have more that 300 users so we don't want to keep updating a manual data.

Regards

KC

[Peter_Cammaert]

You do not need to do this manually! Your LOAD Script can simply add missing combinations of user/period with zero amounts automatically. Due to containing zero amounts in the first place, they will not appear in any chart by default, but will let you impose good behavior when needed.

[jagan]

Hi Jyothish,

It is not possible, we need to create the records for those users with 0 values, then only it is possible to achieve this.  If you attach some sample data we can automate this in script itself.

Regards,

Jagan.

[jyothish8807]

Thanks jagan, i did the same.

[sarfaraz_sheikh]

Dear Jagan,

can u please keep any example for my understanding ....so that it would be more appreciable...

Sarfaraz

[jagan]

Hi,

I don't have the script right now.  May Jyothish Kc can add which he did.

Regards,

Jagan.

[jyothish8807]

Hi Jagan ,

I simply created a additional table with User name, Date, product and Sales and assigned '0' as sales for all months and product then joined concatenated with my master sheet.

Regards

KC

[Peter_Cammaert]

You can mark your own last reply as a correct answer if you want. The big advantage lies in the fact that a correct answer will appear in the same frame as the original post. In that way, visitors that are looking for a solution to a similar problem aren't forced to read the whole thread. Go ahead!

[tyagishaila]

Hi Kc,

You can try this,

if(Datadate, Value, '0')

if you find this is not solution of your problem. than let me know because I have faced this problem . May be I can give more solutions.