topic Re: issue in Pivot table data getting 0 when selection made in QlikView

issue in Pivot table data getting 0 when selection made

Thu, 10 Sep 2015 07:01:20 GMT

Hi ,

I have a Pivot table in my application . e are calculating few formulas on 3, 4 dimension .

We are calculating YTD . There is a Year filter.

When I select 2014 the pivot gets populated and if I click anywhere in pivot the data gets selected accordingly .

However when I select 2015. the pivot get populated too but when I click anywhere in pivot . every calculated formula becomes 0. and all chart goes to no data to display .

My formula is below

=if(GetSelectedCount(Month)=0,
Num((sum({<Year={$(=Only(Year))},Month={"<=$(=max({<Month={$(=Month(today()))}>} Month))"}>}Activity_Target)),'#,##0'),
Num((sum({<Year ={$(=Only(Year))}>} Activity_Target)),'#,##0'))

Please could anyone help me with the above .Its lil urgent

Thanks

Medha

Re: issue in Pivot table data getting 0 when selection made

maleksafa — Thu, 10 Sep 2015 07:06:23 GMT

can you upload a sample document?

Re: issue in Pivot table data getting 0 when selection made

Thu, 10 Sep 2015 07:17:23 GMT

Here is the file .

If we keep Year 2015 and select in pivot its gives everything 0.

Other sheet as well which are not attached here .

if you select 2014 and make selection it works fine .

Thanks for your help in advance

Re: issue in Pivot table data getting 0 when selection made

maleksafa — Thu, 10 Sep 2015 07:35:14 GMT

first thing i discovered is that you have synthetic keys in your schema, you should review that as it might create some problems for you, but this is not the reason why you are getting 0.

the reason for the 0 is the below:

let's take the first expression in your pivot table:

=if(GetSelectedCount(Month)=0,

Num((sum({<Year={$(=Only(Year))},Month={"<=$(=max({<Month={$(=Month(today()))}>} Month))"}>}Activity_Target)),'#,##0'),

Num((sum({<Year ={$(=Only(Year))}>} Activity_Target)),'#,##0'))

since you are not selecting any month, so the expression that will be evaluated is the first one:

Num((sum({<Year={$(=Only(Year))},Month={"<=$(=max({<Month={$(=Month(today()))}>} Month))"}>}Activity_Target)),'#,##0')

the problem resides is the Month part, since you are taking the Month of Today which is returning 9, however if you add a list box now and put Month, select year 2015 and select any line, you will see that month 9 is not a possible value so the part " Month={"<=$(=max({<Month={$(=Month(today()))}>} Month))"} " will not work and will return null.

however if you don't select any line, just 2015 it will work because at least one of lines have a possible value in 9.

hope it was clear.

Re: issue in Pivot table data getting 0 when selection made

Thu, 10 Sep 2015 07:39:36 GMT

Thanks a ton.

How can I correct this any guidance.

Re: issue in Pivot table data getting 0 when selection made

Thu, 10 Sep 2015 07:45:06 GMT

I have one more question my formulas has <=month(today()).

Should it not bring the earlier months value .

I am sorry I am lil new to qlikview . hence asking questions.

Re: issue in Pivot table data getting 0 when selection made

maleksafa — Thu, 10 Sep 2015 07:49:45 GMT

month(Today()) will get the current month, so in our case 9.

however when you are using it in this syntax Month = month(Today()) it will not work on all the line, because some line don't have month 9 as possible value.

to fix it, i would say use the below expression

Num((sum({<Year={$(=Only(Year))},Month={"<=$(=num(Month(today())))"}>}Activity_Target)),'#,##0')

i have changed the Month part, i am not sure why you were using the max(Month) instead.

Re: issue in Pivot table data getting 0 when selection made

Thu, 10 Sep 2015 17:52:03 GMT

Thank you Malek for your prompt help ,

The solution worked.

However synthetic Key was required that's why its there