Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi guys,
I am new to qlik sense.
I have one requirement.
Data look likes below I have 3 fields
Date,. status ,. Sales
05/20/2022. Pending. 50000
05/20/2022. Pending. 40000
05/20/2022. Pending. 30000
08/ 18/ 2022. Pending. 5000
08/18/2022. Pending. 8000
,.................................
........................... ect...
My requirement is. I want to display only old 25 pending sales
Required output.
05/20/2022. Pending. 50000
05/20/2022. Pending. 40000
05/20/2022. Pending. 30000
08/ 18/ 2022. Pending. 5000
08/18/2022. Pending. 8000.
I used date and status. Dimension
Measure is maxstring (sales)
Applied button 25 limitations records in date dimension.
I am getting single date only
05/20/2022. Pending. 50000
08/ 18/ 2022. Pending. 5000..
Please help me this.
Thanks in advance 🙏
Regards,
Subba
OK this seems a weird one.
Breaking it down use of MaxString is gonna find the last text value (Why use a text check on a numeric sales value?) by whatever you group it by in your case Date so you will be getting single dates because that's what you're grouping by.
What you need to do from your request is work out how old your orders are so Today() - Order Date should get you number of days the order has been pending. Then have your straight table show Orders, Date, Status No of days (sorted descending) and sales with the limit set to 25 to show the 25 oldest Orders.
@Subbu55 Please see the below code that I have used in the script Editor. This logic is only old 3 Pending status, you can apply the same logic for 25 old Pending status.
NoConcatenate
Temp:
Load Date(Date#(Date,'MM/DD/YYYY'),'MM/DD/YYYY') as Date,
status,
Sales
inline [
Date, status, Sales
05/20/2022, Pending, 50000
04/20/2022, Delivered, 40000
06/20/2022, Pending, 30000
08/18/2022, Pending, 5000
07/18/2022, Departed, 8000
09/20/2022, Delivered, 50000
10/20/2022, Pending, 40000
11/20/2022, Delivered, 30000
12/18/2022, Pending, 5000
01/18/2022, Departed, 8000];
NoConcatenate
Temp1:
Load *,
Rowno() as Record_Number
Resident Temp
where status='Pending'
order by Date;
NoConcatenate
Temp2:
Load *
Resident Temp1
where Record_Number<='3';
Drop table Temp,Temp1;
Exit Script;
If this resolves your issue, please like and accept it as a solution.