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

Understanding specific variables

Hello,

Someone made the following variable but I can't seem to interpret correctly how it works.


s.ClearCalanderFields : ='['&concat({<$Table={'Calendar'}>}$Field,']=,[')&']='


If I have a Table "Calander" with fields MonthYear, Year and MonthSerial_ in it, would the result be [MonthYear]=,[Year]=,[MonthSerial_]=,[]=

What does this variable exactly do?


After that I have another variable


f.SetYTD  : $(s.ClearCalendarFields),Year= {$(=max(Year)-$1)}, [MonthSerial_] = {"<=$(=Max([MonthSerial_])-($1*12))"}


This starts with the previous Variable and adds some other elements : pick Max Year and pick max MonthSerial and the ones before it. How does this exectly work? What is the output?

The idea was to use this Variable in a Set Analysis like : sum( {<$(f.SetYTD(0)) >} Sales) to limit the data to only Year to Date Sales. When doing sum( {<$(f.SetYTD(1)) >} Sales) it would give the Year to Date Sales of the previous year. How does this mechanisme work that when i put 1 it gives the YtD sales of the previous year?


Thanks in advance for the help!


1 Solution

Accepted Solutions
felipedl
Partner - Specialist III
Partner - Specialist III

Hi Jonha,

The first variable s.ClearCalanderFields, only gets the fields of the specified table, on your case Calendar, so by making that, you'll be able to tell that it has the

  • [MonthYear]
  • [Year]
  • [MonthSerial_]
  • []

Fields.

The second one, f.SetYTD is using the first one as clearing the fields on the set analysis that is used in an expression.

So, by expanding the dollar sign on the f.SetYTD variable, you'll get

[MonthYear]=,[Year]=,[MonthSerial_]=,[]=,Year= {$(=max(Year)-$1)}, [MonthSerial_] = {"<=$(=Max([MonthSerial_])-($1*12))"}

So what it's actually doing is ignoring the

[MonthYear]=,[Year]=,[MonthSerial_]=,[]=,Year=

fields, and then setting the others like:

Year= {$(=max(Year)-$1)}, [MonthSerial_] = {"<=$(=Max([MonthSerial_])-($1*12))"}

In my opinion, the first parameters shouldn't need to be duplicated, since you're setting them anyways, must be some logic to it, but i'm not seeing why they're duplicated, could be only something like:

sum({<[MonthYear]=,[]=,Year= {$(=max(Year)-$1)}, [MonthSerial_] = {"<=$(=Max([MonthSerial_])-($1*12))"}>}Sales)

(In this case, the [MonthYear]=,[]= would be ignored, while the other fields would be set according to the expression.

For example, if you want a sum of sales ignoring the fields of the calendar, you would do

sum({<$(s.ClearCalanderFields)>}Sales)

and for year to date

sum({<$(f.SetYTD)>}Sales)

View solution in original post

3 Replies
felipedl
Partner - Specialist III
Partner - Specialist III

Hi Jonha,

The first variable s.ClearCalanderFields, only gets the fields of the specified table, on your case Calendar, so by making that, you'll be able to tell that it has the

  • [MonthYear]
  • [Year]
  • [MonthSerial_]
  • []

Fields.

The second one, f.SetYTD is using the first one as clearing the fields on the set analysis that is used in an expression.

So, by expanding the dollar sign on the f.SetYTD variable, you'll get

[MonthYear]=,[Year]=,[MonthSerial_]=,[]=,Year= {$(=max(Year)-$1)}, [MonthSerial_] = {"<=$(=Max([MonthSerial_])-($1*12))"}

So what it's actually doing is ignoring the

[MonthYear]=,[Year]=,[MonthSerial_]=,[]=,Year=

fields, and then setting the others like:

Year= {$(=max(Year)-$1)}, [MonthSerial_] = {"<=$(=Max([MonthSerial_])-($1*12))"}

In my opinion, the first parameters shouldn't need to be duplicated, since you're setting them anyways, must be some logic to it, but i'm not seeing why they're duplicated, could be only something like:

sum({<[MonthYear]=,[]=,Year= {$(=max(Year)-$1)}, [MonthSerial_] = {"<=$(=Max([MonthSerial_])-($1*12))"}>}Sales)

(In this case, the [MonthYear]=,[]= would be ignored, while the other fields would be set according to the expression.

For example, if you want a sum of sales ignoring the fields of the calendar, you would do

sum({<$(s.ClearCalanderFields)>}Sales)

and for year to date

sum({<$(f.SetYTD)>}Sales)

Anonymous
Not applicable
Author

Hello Felip,

Thank you for the helpful response. I have 2 more questions though :

  1. How does in a Set Analysis the $(f.SetYTD(0)) give the Year to Date of this year, and the $(f.SetYTD(1)) give the Year to Date of past year? What part of the forumula does this and how does this work?
  2. In the s.ClearCalanderFields, do I have it right that it'll always create at the end following part "[]=" (an empty field), does this have any impact on my data?

Thanks!

felipedl
Partner - Specialist III
Partner - Specialist III

Hi Jonah

1. This part of the code

[MonthSerial_] = {"<=$(=Max([MonthSerial_])-($1*12))"}

will get the parameter (in this case $1 is a parameter you can input) and multiply it *12 and take it of the MonthSerial max, so in this case, if you do $(f.SetYTD(0)) its, making

[MonthSerial_] = {"<=$(=Max([MonthSerial_])-(0*12))"} // Getting the year to date

as to $(f.SetYTD(1))

[MonthSerial_] = {"<=$(=Max([MonthSerial_])-(1*12))"} // Getting the previous year

and thus, giving the previous year amount.


2. For the [] field, I can't quite understand what it does on your data model, maybe its a control flag of some sort.

See if there's any data for this field.

In my opinion if it's empty for all cases, it can be taken out.

Felipe.