topic Last year in set analysis expression in the load script in QlikView

Last year in set analysis expression in the load script

ariaseze — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Last year in set analysis expression in the load script

m_woolf — Wed, 17 Jan 2018 20:53:23 GMT

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

Re: Last year in set analysis expression in the load script

zebhashmi — Wed, 17 Jan 2018 21:10:53 GMT

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

Regards

Re: Last year in set analysis expression in the load script

ariaseze — Thu, 18 Jan 2018 13:59:59 GMT

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

Regards!

Re: Last year in set analysis expression in the load script

zebhashmi — Thu, 18 Jan 2018 18:23:53 GMT

Maybe this thread can help you.

Last 2 months with current selected month

Re: Last year in set analysis expression in the load script

ariaseze — Fri, 19 Jan 2018 14:05:03 GMT

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.

Re: Last year in set analysis expression in the load script

ariaseze — Fri, 19 Jan 2018 14:18:23 GMT

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

Re: Last year in set analysis expression in the load script

zebhashmi — Fri, 19 Jan 2018 18:23:31 GMT

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

Re: Last year in set analysis expression in the load script

ariaseze — Fri, 19 Jan 2018 18:36:22 GMT

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!

Re: Last year in set analysis expression in the load script

zebhashmi — Fri, 19 Jan 2018 19:56:33 GMT

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

Re: Last year in set analysis expression in the load script

ariaseze — Fri, 19 Jan 2018 20:06:23 GMT

But that is precisely the problem, in order to be maintainable and not indecipherable code, I need to solve it in the load script, and NOT in the text object, that is to say, I need to use the variable created in the script in the text object because the code in the text object itself is already complex.

Re: Last year in set analysis expression in the load script

zebhashmi — Fri, 19 Jan 2018 20:16:51 GMT

I think you can use this formula to pass it to Variable. Do fix the Data in Table so that it has associations and try.

I think you would have to have a variable after a load of the table. let me check

I think you will need LET instead of SET