Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Hi, I have managed to solve the issue yay!
I changed to code to:
=Count( {$<we_date_num = {$(vWeekEndingMax)} >} product_name )
It was a case of adding an equal sign before count but this still showed as 0 but when I added an equal sign to my other variable vWeekEndingMax
=Max(we_date_num) then this started working
I would love to know what I should be using as I have been looking through the forums and seen so many examples without the equal sign so would love an explanation.
Thank you all for your help
I think you're missing quoutes:
Count({$<Date={"$(= max (we_date))"}>}product_name)
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.
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.
Hi DavidM, I have checked we_date under Data Model viewer and within Tags I can see $numeric $integer $timestamp $date
This is strange, also your count is showing sum. If you add column Max(we_date) to table what do you see?
When I add Max(we_date) it shows me the date 13/09/2019 after I change the Number formatting to Date.
I suppose you need to count distinct products. Date is ok, set analysis is ok
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.
check if this works
COUNT({<we_date_num = {"$(vWeekEndingMax)"}>}product_name)