Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
...Hi,
I am creating a Qlikview dashboard, for which only 1 Excel table has been loaded. The table looks something like this
Reporting Date | Customer ID | Status | XXX | YYY | ZZZ |
---|---|---|---|---|---|
week 6 | 111 | Preparing proposal | |||
week 6 | 444 | Preparing contract | |||
Week 7 | 222 | Preparing contract | |||
week 7 | 333 | Preparing contract | |||
week 7 | 111 | Proposal sent | |||
week 8 | 444 | Contract signed | |||
.... |
Now I'd like to design a table in Qlikview that only shows the values for each Customer ID with the last Reporting Date. So it should look like this:
Reporting Date | Customer ID | Status | XXX | YYY | ZZZ |
---|---|---|---|---|---|
week 7 | 111 | Proposal sent | |||
week 7 | 222 | Preparing contract | |||
week 7 | 333 | Preparing contract | |||
week 8 | 444 | Contract signed | |||
... |
How can I achieve this?
Use Customer ID as dimension and then expressions like
=FirstSortedValue(Status, -SubField([Reporting Date],' ',-1))
=FirstSortedValue([Reporting Date], -SubField([Reporting Date],' ',-1))
Customer ID | =FirstSortedValue(Status, -SubField([Reporting Date],' ',-1)) | =FirstSortedValue([Reporting Date], -SubField([Reporting Date],' ',-1)) |
---|---|---|
Contract signed | week 8 | |
111 | Proposal sent | week 7 |
222 | Preparing contract | Week 7 |
333 | Preparing contract | week 7 |
444 | Contract signed | week 8 |
edit:
You can also create a field from Reporting Date in your script, using
LOAD
...
Subfield([Reporting Date],' ',-1) as WeekNo ,
Then you can just use WeekNo as sort weight in FirstSortedValue (or try to interprete the Reporting Date as Date using MakeWeekDate().
Hope this helps,
Stefan
the 2 expressions are
MaxString([Reporting Date])
subfield(concat(Status, '@', aggr(MaxString([Reporting Date]),[Customer ID])),'@',1)
Here is script solution
Data:
LOAD [Reporting Date],
[Customer ID],
Status
FROM
[https://community.qlik.com/thread/210992]
(html, codepage is 1252, embedded labels, table is @1)
where not wildmatch([Reporting Date] ,'*.*') ;
New:
NoConcatenate
LOAD *,
if([Customer ID]<> Peek([Customer ID],(RowNo()-1)-1),1,0) as Flag
Resident Data
Order by [Customer ID],[Reporting Date] desc;
DROP Table Data;
Expressions:
=Only({<Flag={1}>}[Reporting Date])
=Only({<Flag={1}>}Status)