topic How to create static dimension in pivot table (QV) in QlikView

How to create static dimension in pivot table (QV)

jyothish8807 — Tue, 28 Apr 2015 05:56:36 GMT

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

Re: How to create static dimension in pivot table (QV)

Anonymous — Tue, 28 Apr 2015 06:01:55 GMT

Hi,

Uncheck Suppress zero values option in the presentation tab.

Regards,

Greeshma

Re: How to create static dimension in pivot table (QV)

jyothish8807 — Tue, 28 Apr 2015 06:24:06 GMT

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

Re: How to create static dimension in pivot table (QV)

Anonymous — Tue, 28 Apr 2015 06:27:56 GMT

Can you share your QVW?

Re: How to create static dimension in pivot table (QV)

jyothish8807 — Tue, 28 Apr 2015 06:41:05 GMT

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

Re: How to create static dimension in pivot table (QV)

jyothish8807 — Tue, 28 Apr 2015 07:17:09 GMT

Any Suggestions ? jagan‌

Re: How to create static dimension in pivot table (QV)

settu_periasamy — Tue, 28 Apr 2015 07:19:53 GMT

You need like the below, right?

check the attachment.

Re: How to create static dimension in pivot table (QV)

sarfaraz_sheikh — Tue, 28 Apr 2015 07:28:40 GMT

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

Re: How to create static dimension in pivot table (QV)

jyothish8807 — Tue, 28 Apr 2015 07:29:51 GMT

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

Re: How to create static dimension in pivot table (QV)

Peter_Cammaert — Tue, 28 Apr 2015 07:38:15 GMT

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.

Re: How to create static dimension in pivot table (QV)

settu_periasamy — Tue, 28 Apr 2015 07:38:37 GMT

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.

Re: How to create static dimension in pivot table (QV)

jyothish8807 — Tue, 28 Apr 2015 09:18:43 GMT

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

Re: How to create static dimension in pivot table (QV)

Peter_Cammaert — Tue, 28 Apr 2015 09:23:16 GMT

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.

Re: How to create static dimension in pivot table (QV)

jagan — Wed, 29 Apr 2015 05:38:01 GMT

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.

Re: How to create static dimension in pivot table (QV)

jyothish8807 — Wed, 29 Apr 2015 05:40:58 GMT

Thanks jagan, i did the same.

Re: How to create static dimension in pivot table (QV)

sarfaraz_sheikh — Wed, 29 Apr 2015 06:39:56 GMT

Dear Jagan,

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

Sarfaraz

Re: How to create static dimension in pivot table (QV)

jagan — Wed, 29 Apr 2015 06:59:11 GMT

Hi,

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

Regards,

Jagan.

Re: How to create static dimension in pivot table (QV)

jyothish8807 — Wed, 29 Apr 2015 08:13:09 GMT

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

Re: How to create static dimension in pivot table (QV)

Peter_Cammaert — Thu, 30 Apr 2015 07:18:33 GMT

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!

Re: How to create static dimension in pivot table (QV)

tyagishaila — Thu, 30 Apr 2015 07:33:28 GMT

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.