topic Re: set analysis and dates in QlikView

set analysis and dates

anuradhaa — Tue, 03 Mar 2015 09:28:32 GMT

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

Re: set analysis and dates

MK_QSL — Tue, 03 Mar 2015 11:09:52 GMT

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))

Re: set analysis and dates

anuradhaa — Tue, 03 Mar 2015 12:34:25 GMT

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

Re: set analysis and dates

MK_QSL — Tue, 03 Mar 2015 12:48:20 GMT

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

Re: set analysis and dates

anuradhaa — Tue, 03 Mar 2015 12:49:15 GMT

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.

Re: set analysis and dates

anuradhaa — Tue, 03 Mar 2015 12:50:21 GMT

another one more thing.

how can we display month in the dimension.

thanks

Re: set analysis and dates

anuradhaa — Tue, 03 Mar 2015 13:05:00 GMT

pls find attached qvw.

their want to display differance in bar chart

Re: set analysis and dates

MK_QSL — Tue, 03 Mar 2015 13:12:26 GMT

Can you provide some more information on your requirements?

Re: set analysis and dates

anuradhaa — Tue, 03 Mar 2015 13:25:45 GMT

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)