How to have two date for a list box

[#1]

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

[MK_QSL]

Canonical Date

[sunny_talwar]

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.

[#4]

Thanks Sunny. Please find enclosed the input file.

[sunny_talwar]

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'}>}

[#6]

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

[aapurva09]

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.

[sunny_talwar · Accepted solution]

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

[sunny_talwar]

And my name is Sunny, not Sunil

[#10]

Thanks a Lot Sunny & Manish!!!