11 Replies Latest reply: Jan 13, 2017 2:44 AM by pavan kumar

# What is the purpose of ,sales = {'*'}>} ?

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

• ###### Re: What is the purpose of ,sales = {'*'}>} ?

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

• ###### Re: What is the purpose of ,sales = {'*'}>} ?

chk below image

• ###### Re: What is the purpose of ,sales = {'*'}>} ?

Hi All

Thank you very much for your explanation.

Paul

• ###### Re: What is the purpose of ,sales = {'*'}>} ?

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

• ###### Re: What is the purpose of ,sales = {'*'}>} ?

this is actually right answer  Paul

Vikas

• ###### Re: What is the purpose of ,sales = {'*'}>} ?

Kushal,

Check your sum({<status={'*'}>}Sales) will return 300 instead 600 because for ID 3 status value is NULL.

I think if you give  um({<status={'*'}>}Sales) ut return total 600 not ignore Status-->null value--> sales.

• ###### Re: What is the purpose of ,sales = {'*'}>} ?

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

• ###### Re: What is the purpose of ,sales = {'*'}>} ?

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

• ###### Re: What is the purpose of ,sales = {'*'}>} ?

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

• ###### Re: What is the purpose of ,sales = {'*'}>} ?

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

• ###### Re: What is the purpose of ,sales = {'*'}>} ?

I guess I am not sure what you are trying to get Paul. Do you have an expected output in mind?

• ###### Re: What is the purpose of ,sales = {'*'}>} ?

Hi Sunny

i have remove mar sales value from raw data , and when user select current month = 3 , i expect it should display jan till mar sales amount ( in this case it will should display jan till feb value , since march sales amount = 0 ), but now it display zero which i think is not correct.

Paul