Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
In the script I use this expression to calculate if a person has been active for * > 36 month/ > 10 months / < 10 months. The calcultions are based on the latest event-date (BOKDAT) vs the first event-date. Because I do this in the script it become static, I would like to do this dynamic in the app to make the calculation based on the month the user select.
Can that be done?
Left join
Load
Max(Date(BOKDAT,'YYYY-MM-DD')) as [Latest BOKDAT],
Min(Date(BOKDAT,'YYYY-MM-DD')) as [First BOKDAT],
If(Min(Date(BOKDAT,'YYYY-MM-DD')) <= ADDMONTHS(Max(Date(BOKDAT, 'YYYY-MM-DD')),-36), '> 36 Month',
If(Min(Date(BOKDAT,'YYYY-MM-DD')) <= ADDMONTHS(Max(Date(BOKDAT, 'YYYY-MM-DD')),-10), '> 10 Month',
'< 10 Month')) as Time,
INSNR
Resident B Group by INSNR;
Here's an example of how you can solve is in the application. You need to use a chart and not a table box, though.
I'm pretty sure what you want is possible. But I don't understand what you want. Perhaps the attached qvw. If not please explain in more detail what you want to achieve.
I will look into the atteched files from both of you. But let me try to explain a bit better.
I would like the Time-calculaton be based on the month that the user select in the calendar. I have here narrowed the data to one ID that have monthly regulary events from 2010-10 until 2013-05. If then the user select (in the calendar) 2010-11 the Time-calculation should show < 10 months becasue in 2010-11 the Id/person had only been active for 1 month. But if the user select 2013-05 it should show "> 10 months" because then the person has been active for more then 10 months.
The intervalls should be: Less the 10 months / more then 10 months but less then 36 months / more then 36 months
With your help it looks like I have a start to the solution.
In a chart I use the following expression so it compare the first event-date against the "Datum" = Date-field in the calendar. It look like it work.
if
([First BOKDAT]<=AddMonths(Datum,-36),'> 36 Month',
if([First BOKDAT]<=AddMonths(Datum,-10),'> 10 Month',
'< 10 Month'))
For a ID that have the following evendates
2010-01-21
2010-04-01
2013-03-21
If I select 2010-04 it shows < 10 months. And for 2013-03 it shows > 36 months.
The new problem now is that if I select 2013-02 it do not show anything because the Person/ID don´have any event that month (and the connection tohe calendare are based on the event-date). But because there do exist a larger event-date (ie the persons act was open in 2013-02) I would like it count even for this month. Can that be made, I guees in the script with some "makedate" function to fill the act with dates to make a connection to the calendar.
Edit: Maby not makedate but generate/fill the list of event-date so it has atleast one date every month upp to the latest event-date.
The problem with using the same field for selecting a month as the field in the calender is that you make a selection in the calender and you then want to calculate "stuff" outside of this selection.
There are a number of ways to go around this:
I'd go for the no 2. A formula for retrieveng the selected month would be:
=Only({SecondState} ÅrMån)
A general recommendation (which also would make this easier for you) is to have real "dates" behind fields like YearMonth. This you achieve by creating the field ÅrMån as:
Date(MonthStart(Temp_Datum), 'YYYYMM') as ÅrMån
This will for instance allow you to easier calculate how many months back the selected date is as well as a number of other good things. I attach a new file.