Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

paulyeo11
Not applicable

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
Not applicable

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

chk below imageopertator.png

12 Replies
Chanty4u
Not applicable

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

Chanty4u
Not applicable

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

chk below imageopertator.png

kush141087
Not applicable

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

paulyeo11
Not applicable

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

Hi All

Thank you very much for your explanation.

Paul

sunny_talwar
Not applicable

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

paulyeo11
Not applicable

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

sunny_talwar
Not applicable

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

paulyeo11
Not applicable

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

sunny_talwar
Not applicable

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?