Calculate measure in script

[Qliksense_77]

Hi,

The below calculation of occupied beds works in charts.  Is it possible to do the calculation in script?

If(Type='Bed_days' and Date.Year>'2016', (sum(aggr(count( Distinct ID), Date, ID))

+count(Distinct(If(Date_arrival=Date_departure,  ID)))

-count(Distinct(If(Date=Date_departure,  ID)))))

I do the following in the script:

Load

Id,

Type,

Date_arrival,

Date_departure

....

MinMax:

      Load min("Date_arrival") as MinDate,

           Max("Date_departure") as MaxData

      Resident Fact;

      let vMinDate=peek('MinDate',0,'MinMax');

      let vMaxDate=peek('MaxData',0,'MinMax');

      drop table MinMax;

      TempCalendar:

      load Date($(vMinDate)+IterNo()-1) as Date

      AutoGenerate(1) while $(vMinDate)+IterNo()-1 <= $(vMaxDate);

      MasterCalendar:

      Load 

                     Date AS "Date", 

                     week(Date) As Date.Week, 

                     Year(Date) As Date.Year,

                     'Q'&ceil(month(Date)/3) as Date.Quarter,

                     'Q'&ceil(month(Date)/3)&'-'& Year(Date) as Date.QuarterYear,

                     Month(Date) As Date.Month, 

                     Day(Date) As Date.Day,

                     date(monthstart(Date), 'MMM YYYY') as Date.MonthYear

      Resident TempCalendar 

      Order By Date ASC;

IntervalMatch (Date)

LOAD

  dato_arrival,

  "date_departure"

RESIDENT

  Fact;

[Andrei_Cusnir · Accepted solution]

If my understanding is correct, you would like to use the expression inside Data load editor script. This use case scenario unfortunately is not possible. In "Functions in scripts and chart expressions" [1] documentation it is stated that "Many functions can be used in the same way in both data load scripts and chart expressions, but there are a number of exceptions:" and one exception is "Some functions can only be used in chart expressions, denoted by - chart function.". This means that there are functions that can only be used in Chart expressions but you can't use them in script. Additionally, it is also mentioned that there are some functions that can be used both in Chart expressions and script, however even in that use case scenario there could be some differences in parameters and application. 

With that being said and checking the complex expression that you have, I believe it would be quite challenging if not impossible to get the desired outcome. Is there any particular reason that you don't wont to keep using this expression in the Charts? Maybe you can explain what exactly are you trying to achieve and someone can find a more optimal solution to your issue.

I hope that this information was helpful.

---

[1] https://help.qlik.com/en-US/sense/June2020/Subsystems/Hub/Content/Sense_Hub/Scripting/functions-in-s...

[Kushal_Chawda]

@Qliksense_77  If you could share sample data with expected output, we could suggest best way to achieve it.