topic Re: How to have two date for a list box in QlikView

How to have two date for a list box

Thu, 04 Feb 2016 08:55:56 GMT

Dear QV Experts,

In the enclosed document, i have two dates Date Received & Date Offer Sent.

Created Month only for Date Received Column.

Created ListBox for Month which reflects based on the Date Received Column in both the objects [Straight Table & Table Box).

How can i have one list box for Month,

so that the chart which has Date Received should be impacted based on Date Received.

The other control which has Date Offer Sent should be impacted based on Date Offer Sent column only not on the Date Received Column.

Please suggest me on this.

Thanks

Re: How to have two date for a list box

MK_QSL — Thu, 04 Feb 2016 08:57:17 GMT

Canonical Date

Re: How to have two date for a list box

sunny_talwar — Thu, 04 Feb 2016 09:53:54 GMT

The link that Manish has shared will give you step by step dimensions. But if you still have any problems, please share the underlying Excel file, so that one of us here can show you how it can be implemented.

Re: How to have two date for a list box

Thu, 04 Feb 2016 09:58:25 GMT

Thanks Sunny. Please find enclosed the input file.

Re: How to have two date for a list box

sunny_talwar — Thu, 04 Feb 2016 10:22:54 GMT

Check out the attached:

Script:

TestData:

LOAD [Newserv Id],

[Date Received],

[Acknowledgement Sent],

Month([Date Received])as Mnth,

[Date Offer Sent]

FROM

[TestDatas.xlsx]

(ooxml, embedded labels, table is Sheet1);

TestDataResi:

LOAD [Newserv Id],

If(Ceil([Acknowledgement Sent] - [Date Received])-1 >= -1 and Ceil([Acknowledgement Sent] - [Date Received])-1 <=3, '1 - 3 Days',

If(Ceil([Acknowledgement Sent] - [Date Received])-1 >= 4 and Ceil([Acknowledgement Sent] - [Date Received])-1 <=5, '4 - 5 Days',

If(Ceil([Acknowledgement Sent] - [Date Received])-1 >=6, '5+ Days'))) as Acknowledgement,

If(IsNull([Acknowledgement Sent]) ,'Null',Date(If(Len(Trim([Acknowledgement Sent])) = 0 , Today(),' ' ))) AS Ack_Ongoing,

[Date Offer Sent]

Resident TestData;

LINK_TABLE:

LOAD [Newserv Id],

[Date Received] as Date,

'Received' as DateType

Resident TestData;

Concatenate(LINK_TABLE)

LOAD [Newserv Id],

[Date Offer Sent] as Date,

'Offer Sent' as DateType

Resident TestData;

QuartersMap:

MAPPING LOAD

rowno() as Month,

'Q' & Ceil (rowno()/3) as Quarter

AUTOGENERATE (12);

Temp:

Load Min(Date) as minDate,

Max(Date) as maxDate

Resident LINK_TABLE;

Let varMinDate = Num(Peek('minDate', 0, 'Temp'));

Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));

DROP Table Temp;

TempCalendar:

LOAD $(varMinDate) + Iterno()-1 As Num,

Date($(varMinDate) + IterNo() - 1) as TempDate

AutoGenerate 1

While $(varMinDate) + IterNo() -1 <= $(varMaxDate);

MasterCalendar:

Load TempDate AS Date,

week(TempDate) As Week,

Year(TempDate) As Year,

Month(TempDate) As Month,

Day(TempDate) As Day,

YeartoDate(TempDate)*-1 as CurYTDFlag,

YeartoDate(TempDate,-1)*-1 as LastYTDFlag,

inyear(TempDate, Monthstart($(varMaxDate)),-1) as RC12,

date(monthstart(TempDate), 'MMM-YYYY') as MonthYear,

ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter,

Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,

WeekDay(TempDate) as WeekDay

Resident TempCalendar

Order By TempDate ASC;

Drop Table TempCalendar;

When you want to just use Received date, you can add a set analysis -> {<DateType = {'Received'}>} (implemented it in object CH02) and when you want to use sent date then use this -> {<DateType = {'Offer Sent'}>}

Re: How to have two date for a list box

Thu, 04 Feb 2016 10:35:54 GMT

Thanks Sunil for your valued support.

If selected the Mnth - March, Then DateReceived displays correctly but in Date offer Sent, March April & May datas are

shown

Re: How to have two date for a list box

aapurva09 — Thu, 04 Feb 2016 10:42:50 GMT

A solution to this is to Concatenate your transaction table twice.

Load the transaction table and create a flag for Date Received and here use the Date Received field as the common date then concatenate this table with the same transaction table having a flag for Date Offer Sent and here use the Date Offer Sent field as the common date. Now connect the calendar with this common date. Use the flag in the expressions. so, once you select a month now both the dates will show values associated to that selected month.

Re: How to have two date for a list box

sunny_talwar — Thu, 04 Feb 2016 10:43:39 GMT

So like I said you will need to use the set analysis wherever you want to see only that particular date's date. Right now when you select the newly created Month field, it will filter out all those rows where either Month(Date Received) = Month or Month(Date Offer Sent) = Month. To get a chart or list box to display only month(Date Offer Sent) = Month you will have to use a set analysis -> {<DateType = {'Offer Sent'}>}

For instance, I created a list box object with this expression -> =Aggr(Only({<DateType = {'Offer Sent'}, [Date Offer Sent]>}[Date Offer Sent]), [Date Offer Sent])

Re: How to have two date for a list box

sunny_talwar — Thu, 04 Feb 2016 10:44:21 GMT

And my name is Sunny, not Sunil

Re: How to have two date for a list box

Thu, 04 Feb 2016 11:56:45 GMT

Thanks a Lot Sunny & Manish!!!