set analysis and dates

[anuradhaa]

Hi,

i have a data set and it contains

start date and enddate.

see below

start	end		difference
1/12/2015	1/25/2015		13.00
1/20/2015	2/16/2015		27.00
1/6/2015	4/1/2015		85.00
12/3/2014	1/16/2015		44.00
12/3/2014	2/16/2015		75

I have year select box and month select box and used end date in those select boxes.

when user select January,I need to display average difference based on below logic,

1. select records that has start date and end date on January and get the difference

2. If the record has started before January and end on January get the start date as jan 1 and calculate the difference

3. If the record started on January and end after January , consider end date as jan 31 and calculate the difference.

4. If record started before January and end after January , consider start date as jan 1 and end date as jan 30 and calculate the difference.

This will depend on the selected month

so above table become like this

start	end		difference
1/12/2015	1/25/2015		13.00
1/20/2015	1/31/2015		11.00
1/6/2015	1/31/2015		25.00
1/1/2015	1/16/2015		15.00
1/1/2015	1/31/2015		30.00

Is this possible and any one has and idea how to do this in scrip or UI.

Thanks

[MK_QSL]

Use below Script...

Table:

Load

  RowNo() as ID,

  Date(Date#(start,'M/D/YYYY')) as start,

  Date(Date#(end,'M/D/YYYY')) as end,

  difference

Inline

[

  start, end, difference

  1/12/2015, 1/25/2015, 13.00

  1/20/2015, 2/16/2015, 27.00

  1/6/2015, 4/1/2015, 85.00

  12/3/2014, 1/16/2015, 44.00

  12/3/2014, 2/16/2015, 75

];

Calendar:

Load Year(end) as Year, Month(end) as Month;

Load Distinct end Resident Table;

Now create a Straight Table

Dimension

Calculated Dimension

=Aggr(IF(end >= MakeDate(Year,Month), ID),ID)

Expression

For Start

=IF(start < MakeDate(Year,Month), MakeDate(Year,Month), start)

For End

=IF(end > MonthEnd(MakeDate(Year,Month)), MonthEnd(MakeDate(Year,Month)), end)

For Difference

Floor(Column(2)) - Floor(Column(1))

[anuradhaa]

Thanks for you reply and help,

Grate work.

but how can this fix to display data when there is no selection made and multiple month selections?

also only the year selection

[MK_QSL]

Use Max(Month) instead of Month and Max(Year) instead of Year

[anuradhaa]

Hi i have done some edit in the script,

Calendar:

Load end,Year(end) as Year, Month(end) as Month;

Load Distinct end Resident Table;

and that solve the issue.

[anuradhaa]

another one more thing.

how can we display month in the dimension.

thanks

[anuradhaa]

pls find attached qvw.

their want to display differance in bar chart

[MK_QSL]

Can you provide some more information on your requirements?

[anuradhaa]

I know it is difficult to do my requirement in qlikview.

but i'm sure we can do below thing

pls find attached qvw above.

In main tab you can see some records don't have out date.

what i need is to assign values for those recoards based on select month.

if i select January it needs to have jan30

if February it should have feb 9

if no selection it needs to have max(date)