Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All
I notice that below both expression return sames result :-
sum({$<year = {$(=max(year))}, month = {"<=$(=max({<year={$(=max(year))}>} month))"}>} sales)
Sum({$<year = {$(=Max(year)-0)}, month = {"<=$(=Max({<year={$(=Max(year))}, sales = {'*'}>} month))"}>}sales/1000)
May i know what is the advantage of sales = {'*'}>} ?
Paul
chk below image
example:
Sum({<Stock -= {"*Cash*"}>}Value)
This will give you the sum of values excluding the Cash from the dimension.
=Sum({<CiTY= {'*'}>}Sales)
it will give the total . all sales mean sum
chk below image
Lets say you have below Expression
=sum({<status={'*'}>}Sales)
Above expression will return Sales for all the status except NULL values in Status. That means if status field has NULL values then Sales values will not be considered in Sum.
Consider below scenario
ID,status, Sales
1, A,100
2, B,200
3, ,300
So for above data, sum({<status={'*'}>}Sales) will return 300 instead 600 because for ID 3 status value is NULL.
Hope this explains well
Hi All
Thank you very much for your explanation.
Paul
Within your database you seem to have all 12 months available for the year 2016. But only have sales data for March. In order to find the max month for the most recent year (2016) where sales is not null, I have been adding sales {'*'} in your code
Hi Sunny
wow your mind is very powerful , after i analyse word by word , i still blur.
you sales below :-
Within your database you seem to have all 12 months available for the year 2016
I think you mean that my table have Jan till Dec in 2016 ,
you sales below :-
In order to find the max month for the most recent year (2016) where sales is not null,
I think you add syntax to check max month. by make sure the value is not zero.
Any way i will think about it when i am more relax.
wait may be i get it now , Can i summary in below :-
So i think you are just add in the code to make sure that is the max month. in case during march have value in April , then the SET expression will also compute Jan till April as YTD sales ? if April not value , it will compute sales from Jan till Mar Right ? That it the reasons both expression display same YTD value in my example. But my question is How can During March have april sales data ?
Paul
Right, that's what my assumption is. That you won't have April data in March. And once you move into April, you will start having data for April and your expression will adjust
Hi Sunny
I still not quite understand , so i create a simple QV Doc , to demostrate when march sales = 0
Column 1 expression :-
Sum({$<year = {$(=Max(year)-0)}, month = {"<=$(=Max({<year={$(=Max(year))}, sales = {'*'}>} month))"}>}sales)
Column 2 expression :-
sum({$<year = {$(=max(year))}, month = {"<=$(=max({<year={$(=max(year))}>} month))"}>} sales)
When not select any year and month :-
Both expression return correct value.
When select year=2016 and month = 3 :-
I notice that for your expression Column 1 , when march sales = 0 , the expression will return zero , which is not correct.
For column 2 expression it does not have sales = {'*'} , which return YTD sales value 247K , which is correct.
Any comment ?
Enclosed QV Doc for your ref.
Paul
I guess I am not sure what you are trying to get Paul. Do you have an expected output in mind?