Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
paulyeo11
Master
Master

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

1 Solution

Accepted Solutions
Chanty4u
MVP
MVP

12 Replies
Chanty4u
MVP
MVP

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

Chanty4u
MVP
MVP

chk below imageopertator.png

Kushal_Chawda

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

paulyeo11
Master
Master
Author

Hi All

Thank you very much for your explanation.

Paul

sunny_talwar

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

paulyeo11
Master
Master
Author

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

sunny_talwar

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

paulyeo11
Master
Master
Author

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

sunny_talwar

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