topic Re: Can I make this if-expression dynamic in a table? in QlikView

Can I make this if-expression dynamic in a table?

Mon, 09 Sep 2013 07:24:26 GMT

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;

Re: Can I make this if-expression dynamic in a table?

johanlindell — Mon, 09 Sep 2013 07:55:51 GMT

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.

Re: Can I make this if-expression dynamic in a table?

Gysbert_Wassenaar — Mon, 09 Sep 2013 08:22:36 GMT

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.

Re: Re: Can I make this if-expression dynamic in a table?

Mon, 09 Sep 2013 08:46:16 GMT

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

Re: Re: Can I make this if-expression dynamic in a table?

Mon, 09 Sep 2013 09:21:45 GMT

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.

([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.

Re: Can I make this if-expression dynamic in a table?

johanlindell — Mon, 09 Sep 2013 11:56:58 GMT

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:

make a new year-month field that is not connected and use this value to determine the range to be used
use the same field, but have a listbox in an "Alternate State" and use the selection in the Alternate Stats to determine the range to be used
Use set expressions to override the selections made in the YearMonth-field. This may be tricky as not all formulas allow Set Expression. Dimensions does not work with Set Expressions unless you make formulas here also.

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.