issue in Pivot table data getting 0 when selection made

[#1]

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

[maleksafa]

can you upload a sample document?

[#3]

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

[maleksafa]

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.

[#5]

Thanks a ton.

How can I correct this any guidance.

[#6]

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.

[maleksafa · Accepted solution]

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.

[#8]

Thank you  Malek  for your prompt  help ,

The solution worked.

However synthetic Key was required that's why its there