Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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;
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.
---
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.
---
@Qliksense_77 If you could share sample data with expected output, we could suggest best way to achieve it.