Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi @petter , @Nicole-Smith , @Gysbert_Wassenaar
I am trying to plot a tree map using below values in a table and color it using expression.
But the problem i am running is that i need to plot status only for the given cob date and Name combination, where Version and Run Date are maximum from the available values and Type = Batch.
With my current expression, its taking both C & F values for 29-Mar-2019 - David instead of just getting C (COB= 29-Mar-2019, Version = 16, Run Date = 5-Jul-2019, Name = David, Type = Batch)
COB | Ver | Run Date | Name | Type | Status |
29-Mar-19 | 16 | 5-Jul-19 | David | Batch | C |
29-Mar-19 | 16 | 5-Jul-19 | David | Batch | C |
29-Mar-19 | 16 | 5-Jul-19 | David | Batch | C |
29-Mar-19 | 15 | 4-Jul-19 | David | Batch | C |
29-Mar-19 | 11 | 3-Jul-19 | David | Batch | C |
28-Mar-19 | 10 | 4-Jul-19 | David | Adhoc | F |
28-Mar-19 | 10 | 3-Jul-19 | David | Batch | F |
Basically how do i get this from above ..
Status |
C |
(Basically, trying to understand, how do i write expression for something like ...
Select distinct status from TABLE
where COB= max(cob) and Version = max(version) and Run Date = max(run Date) and Type = Batch
You can create a table in Qlik Sense and add a single measure with this expression (and no dimensions):
Only(DISTINCT
{<
COB={'$(=Date(Max(COB)))'}
,Ver={$(=Max(Ver))}
,[Run Date]={'$(=Date(Max([Run Date])))'}
,Type={'Batch'}
>}
Status
)
This also seems to work:
Concat( DISTINCT
{<Ver={"=Ver=$(=Max(Ver)) AND COB='$(=Date(Max(COB)))' AND [Run Date]='$(=Date(Max([Run Date])))' AND Type='Batch'"
}>}
Status)
if below is my data
COB,Ver,RunDate ,Name,Type,Status
3/29/2019,16,7/5/2019,David,Batch,C
3/29/2019,16,7/5/2019,David,Batch,C
3/29/2019,16,7/5/2019,David,Batch,C
3/29/2019,15,7/4/2019,David,Batch,C
3/29/2019,11,7/3/2019,David,Batch,C
3/28/2019,10,7/4/2019,David,Adhoc,F
3/28/2019,10,7/3/2019,David,Batch,F
this is what i did on Straight table chart
used COB,Ver,RunDate ,Name,Type as dimensions and Status as expression
COB --> =Aggr(Date(max(COB)),Name)
Ver --> =Aggr(max(Ver),Name)
RunDate --> =Aggr(Date(max(RunDate)),Name)
Name --> Name
Type --> Type
Expressions:
Status --> Status
Result I attached to this message
You can create a table in Qlik Sense and add a single measure with this expression (and no dimensions):
Only(DISTINCT
{<
COB={'$(=Date(Max(COB)))'}
,Ver={$(=Max(Ver))}
,[Run Date]={'$(=Date(Max([Run Date])))'}
,Type={'Batch'}
>}
Status
)
This also seems to work:
Concat( DISTINCT
{<Ver={"=Ver=$(=Max(Ver)) AND COB='$(=Date(Max(COB)))' AND [Run Date]='$(=Date(Max([Run Date])))' AND Type='Batch'"
}>}
Status)
@petter .. thanks for this .. I tried the expressions
=Only(DISTINCT
{<
[FEED_REPORT_STATUS_LOG_QV.COB_DATE]={'$(=Date(Max([FEED_REPORT_STATUS_LOG_QV.COB_DATE])))'}
,[FEED_REPORT_STATUS_LOG_QV.VERSION_NO]={'$(=Max([FEED_REPORT_STATUS_LOG_QV.VERSION_NO]))'}
,[FEED_REPORT_STATUS_LOG_QV.RUN_DATE]={'$(=Date(Max([FEED_REPORT_STATUS_LOG_QV.RUN_DATE])))'}
,[FEED_REPORT_STATUS_LOG_QV.EVENT_TYPE]={'Batch'}
>}
[FEED_REPORT_STATUS_LOG_QV.STATUS]
)
but getting invalid dimension error .. 😐
I tried the expressions I supplied with test data on my own and it worked well for me. If you want me to help me please provide a sample application. The error message doesn't seem to indicate a problem with the expressions unless you put them into a dimension and not a measure... could you please check that you use the expressions as measures?
Please click like if you find any answer helpful as this is the only way you can give "real credit" on this forum. 🙂