Find Status for min date

[lakshmittpd]

Product	Date Type	Date Type ID	Date	Status
abc	Order Date HYD	111	22/11/2021	Open
abc	Order Date CHE	112	2/11/2021	Draft
abc	Order Date MUM	113	20/11/2021	Assume Completed
abc	Order Date DEL	114	22/10/2021	Done
abc	First Orderdate	110	22/10/2021	Open
abc	Delivery Date HYD	121	25/11/2021	Draft
abc	Delivery Date CHE	122	10/11/2021	Assume Completed
abc	Delivery Date MUM	123	22/11/2021	Done
abc	Delivery Date DEL	124	25/10/2021	Open
abc	First Delivery Date	120	25/10/2021	Draft
bcd	Order Date HYD	111	5/7/2022	Assume Completed
bcd	Order Date CHE	112	5/6/2022	Done
bcd	Order Date MUM	113	4/6/2022	Open
bcd	Order Date DEL	114	3/7/2022	Draft
bcd	First Orderdate	110		Assume Completed
bcd	Delivery Date HYD	121	15/7/2022	Done
bcd	Delivery Date CHE	122	15/6/2022	Open
bcd	Delivery Date MUM	123	14/6/2022	Draft
bcd	Delivery Date DEL	124	13/7/2022	Assume Completed
bcd	First Delivery Date	120		Done

Hi All,

I have a requirement, where for each product we have different order dates. in qliksense report I need to show order dates, and if first order date is not avaialble then show mininum of order dates. As my data have orderdates and Delivery dates....if first order date is null then take minimum of Hyd, Che,Mum and Del order dates and similarly if first delivery date is null then take minimum of Hyd, Che,Mum and Del Delivery dates. I also want to get status corresponding to the minimum date. Could you please help me with it.

[vikasmahajan]

Hi

Try some thing like this :

=Count( {<[Date] = {"<=$(=Date(Today(), 'DD/MM/YYYY'))"},Status={'Open'}>}  [Date Type ID] )

keep dimensions  Product, Date Type in chart

Vikas

[lakshmittpd]

I am looking for the status of minimum date

[Kushal_Chawda]

@lakshmittpd  would you like to put some logic in script or you want to completely achieve it from front end only? Just to note that frond end expression could become complex in this scenario

[lakshmittpd]

If there is any possibility in script...then I can go with script.....I got the logic for getting minimum date in front end...getting status of that date is what the issue is now....

Dayname( If ([Date Type ID]=110,

    alt(only(Date), min({<[Date Type ID]={111, 112, 113, 114}>}total <Product> Date)) ,

  if([Date Type ID]=120,

    alt(only(Date), min({<[Date Type ID]={121, 122, 123, 124}>}total <Product> Date)),

     only(Date))))

this is what I used to get min date

[Kushal_Chawda]

@lakshmittpd  try below

Data:
Load *, if(match([Date Type ID],110,111,112,113,114),'Order',
       if(match([Date Type ID],120,121,122,123,124),'Delivery')) as Type 
Inline [
Product	Date Type	Date Type ID	Date	Status
abc	Order Date HYD	111	22/11/2021	Open
abc	Order Date CHE	112	2/11/2021	Draft
abc	Order Date MUM	113	20/11/2021	Assume Completed
abc	Order Date DEL	114	22/10/2021	Done
abc	First Orderdate	110	22/10/2021	Open
abc	Delivery Date HYD	121	25/11/2021	Draft
abc	Delivery Date CHE	122	10/11/2021	Assume Completed
abc	Delivery Date MUM	123	22/11/2021	Done
abc	Delivery Date DEL	124	25/10/2021	Open
abc	First Delivery Date	120	25/10/2021	Draft
bcd	Order Date HYD	111	5/7/2022	Assume Completed
bcd	Order Date CHE	112	5/6/2022	Done
bcd	Order Date MUM	113	4/6/2022	Open
bcd	Order Date DEL	114	3/7/2022	Draft
bcd	First Orderdate	110	 	Assume Completed
bcd	Delivery Date HYD	121	15/7/2022	Done
bcd	Delivery Date CHE	122	15/6/2022	Open
bcd	Delivery Date MUM	123	14/6/2022	Draft
bcd	Delivery Date DEL	124	13/7/2022	Assume Completed
bcd	First Delivery Date	120	 	Done

abd	Order Date HYD	111	5/7/2022	Assume Completed
abd	Order Date CHE	112	5/6/2022	Done
abd	Order Date MUM	113	4/6/2022	Open
abd	Order Date DEL	114	3/7/2022	Draft
abd	First Orderdate	110	 	Assume Completed
abd	Delivery Date HYD	121	15/7/2022	Done
abd	Delivery Date CHE	122	15/6/2022	Open
abd	Delivery Date MUM	123	14/6/2022	Draft
abd	Delivery Date DEL	124	13/7/2022	Assume Completed
abd	First Delivery Date	120	13/7/2022 	Done](delimiter is '\t');

Left Join(Data)
Load Distinct Product,
              Type,
              1 as _IsFirstDateExists
Resident Data
where match([Date Type ID],110,120) and len(trim(Date))>0;

MinDate:
Load distinct  Product,
    [Date Type ID],
    1 as MinDateFlag
Resident Data
where _IsFirstDateExists=1 and match([Date Type ID],110,120);

Concatenate(MinDate)
Load Product,
     Type,
     FirstSortedValue([Date Type ID],Date) as [Date Type ID],
     1 as MinDateFlag
Resident Data
where _IsFirstDateExists<>1 and not match([Date Type ID],110,120)
Group by Product,Type;

Left Join(Data)
Load Product,
     [Date Type ID],
     MinDateFlag
Resident MinDate;

drop Table MinDate;

drop Fields _IsFirstDateExists;

Then use MinDateFlag in set analysis to filter out relevant record.

=sum({<MinDateFlag={1}>}Sales)