Finding out values using multiple conditions for a chart

[klrameet]

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

[bharathadde]

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

[petter · Accepted solution]

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)

[klrameet]

@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 ..  😐

[petter]

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?

[petter]

Please click like if you find any answer helpful as this is the only way you can give "real credit" on this forum. 🙂