Count Based on Max Week Ending Date

[aftabn10]

Hi, I am new to the world of Qlik and I am trying to develop a dashboard with a few measures. The first measure that I would like is to display a count of a column based on the max week ending date. The following is an example of the data (based on an sql query that I have)

we_date | count(*)

2019-09-06 | 914
2019-09-13 | 416

I then wrote the following expression:

Count({$<Date={$(= max (we_date))}>}product_name)

but this displays a total of 1330 which is a sum of 914+416 so not sure where I am going wrong.

I would appreciate it if somebody could advise on how I could fix this.

Thanks in advance.

[DavidM]

I think you're missing quoutes:

Count({$<Date={"$(= max (we_date))"}>}product_name)

[aftabn10]

Hi DavidM thanks for your response. Unfortunately adding the quotes made no difference. I am starting to think if this has anything to do with the data type of the we_date column as I just don't get why the max element isn't being picked up.

[DavidM]

Then you need to check if your field is in $timestamp $date format - you can see that in data model for example (if not use Date# function). Also you could try assigning Max value to variable and check if that variable gets right value.

[aftabn10]

Hi DavidM, I have checked we_date under Data Model viewer and within Tags I can see $numeric $integer $timestamp $date

[DavidM]

This is strange, also your count is showing sum. If you add column Max(we_date) to table what do you see?

[aftabn10]

When I add Max(we_date) it shows me the date 13/09/2019 after I change the Number formatting to Date.

[DavidM]

I suppose you need to count distinct products. Date is ok, set analysis is ok

[aftabn10]

Hi DavidM, quick update. I managed to write the following definition in a variable vTotalOrders:

COUNT({<we_date_num = {"43721"}>}product_name)

and this gives me the numbers that I require, although I am still having an issue applying a max to the date as when I add that it changes all the results to 0.

I also added another variable vWeekEndingMax with the definition Max(we_date_num) and when I add this to the dashboard it displays the date fine so this works but when I change the above definition to:

COUNT({<we_date_num = vWeekEndingMax>}product_name)

then this changes it all to 0 again.

Btw we_date_num is an additional column I added into my table with the datatype int within the sql query I wrote. The reason for this was because I thought with the date value it might still be seeing as text/string hence it wasn't working.

[qlik_venu]

check if this works

COUNT({<we_date_num = {"$(vWeekEndingMax)"}>}product_name)