Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
I am looking for the status of minimum date
@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
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
@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)