Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
ariaseze
Partner - Contributor III
Partner - Contributor III

Last year in set analysis expression in the load script

Hi, experts!

I have the following inconvenience, I need the last year before the selected one and then use it in the set analysis of another variable, all this inside the load script.

I tried this but only work if I use the content of variable directly on text object but not work if I use "Cant" in other formula in load script:


([Year] is a Field).

SET Cant= COUNT({<MonthYear,Year={$(=MAX([Year])-1)}>}  DISTINCT IF(Date_Holiday >= ($(vInv_Date)) ) );      (Work on Text Object)


SET OtherFormula = SUM ( $(A) + $(Cant) ) ;     (Not Work in Load Script)

Thanks in advance!

SET Cant= COUNT({<MonthYear,Year={$(=MAX([Year])-1)}>}  DISTINCT IF(Date_Holiday >= ($(vInv_Date)),)); SET OtherFormula = SUM ( $(A) + $(Cant) ) ;
11 Replies
m_woolf
Master II
Master II

I don't believe that set analysis works in script.

zebhashmi
Specialist
Specialist

I think it can be achieved what you are asking for in a script, but it would not be through set analyses.

Regards

JH

ariaseze
Partner - Contributor III
Partner - Contributor III
Author

Hi zebhashmi, thanks for answering, any clue how to deal with this? because I've tried everything I could imagine without success.

Regards!

zebhashmi
Specialist
Specialist

Maybe this thread can help you.

Last 2 months with current selected month

ariaseze
Partner - Contributor III
Partner - Contributor III
Author

Hi zebhashmi‌, thanks again for answering, but I still can't achieve my goal, I attach a very simplified example of what I need to do, there are 2 tries I made but no successful results.

For a question of maintenance in time it is not viable to use the code that I need to work in the script, directly in the text object, because it is already complex enough so I need to solve it with a variable in the script.

If you can give me some light on this, I'll be very grateful, because I've been spending some days for it.

ariaseze
Partner - Contributor III
Partner - Contributor III
Author

Do you have any idea how to do this without set analysis? below I attached an qvw example. Thanks!

zebhashmi
Specialist
Specialist

Sorry About that Can you share qvf file i do not have access right now on QVW

ariaseze
Partner - Contributor III
Partner - Contributor III
Author

I don´t know what is QVF extension, but I can share the code:

In the script:

Table:

LOAD * INLINE

[Date, Year

01/01/2017, 2017

02/01/2017, 2017

03/01/2017, 2017

04/01/2017, 2017

01/01/2018, 2018

02/01/2018, 2018

03/01/2018, 2018

04/01/2018, 2018

];

Table2:

LOAD * INLINE

[DateHoliday

01/01/2017

03/01/2017

01/01/2018

03/01/2018

];

//SET Cant = COUNT(    {<Year={$(=MAX([Year])-1)}>}                          DISTINCT IF([DateHoliday] = [Date], [DateHoliday])) ;      //(Work on Text Object but not Here)

  SET Cant = COUNT(    {<Year={$(=Year(addyears(MAX([Date]), - 1))) } >}      DISTINCT IF([DateHoliday] = [Date], [DateHoliday])) ;      //(Work on Text Object but not Here)

SET OtherFormula = ( 10 + $(Cant) ) ;     //(I need to use the variable created above in another formula in the load script.)

In the workspace:

• Select Year 2018 from the field.

• In a text object paste that:

='Variable '&'$'&'(Cant) should display 2 for year 2018: ' & $(Cant) & ' (Not Work)' &'

Expression content of the variable '&'$'&'(Cant) in text object: ' & COUNT({<Year={$(=MAX([Year])-1)}>}  DISTINCT IF([DateHoliday] = [Date], [DateHoliday]))

& ' (Work)

Variable '&'$'&'(OtherFormula) should display 12 for year 2018: ' & $(OtherFormula) & ' (Not Work)'

Should display like this picture:

Picture..png

Thanks in advance!

zebhashmi
Specialist
Specialist

Try this

Table:

LOAD * INLINE

[Date, Year

01/01/2017, 2017

02/01/2017, 2017

03/01/2017, 2017

04/01/2017, 2017

01/01/2018, 2018

02/01/2018, 2018

03/01/2018, 2018

04/01/2018, 2018

];

Table2:

LOAD * INLINE

[DateHoliday, Year

01/01/2017,2017

03/01/2017,2017

01/01/2018,2018

03/01/2018,2018

];

and in Text object

=count(Year(DateHoliday))

=10+count(Year(DateHoliday))