Last year in set analysis expression in the load script

[ariaseze]

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) ) ;

[m_woolf]

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

[zebhashmi]

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]

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

Regards!

[zebhashmi]

Maybe this thread can help you.

Last 2 months with current selected month

[ariaseze]

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]

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

[zebhashmi]

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

[ariaseze]

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:

Thanks in advance!

[zebhashmi]

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))