Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
can you upload a sample document?
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
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.
Thanks a ton.
How can I correct this any guidance.
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.
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.
Thank you Malek for your prompt help ,
The solution worked.
However synthetic Key was required that's why its there