Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
The #1 reason QlikView customers adopt Qlik Sense is a desire for a modern BI experience. Read More
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor III
Contributor III

Count Based on Max Week Ending Date

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.

1 Solution

Accepted Solutions
Highlighted
Contributor III
Contributor III

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

View solution in original post

12 Replies
Highlighted
Partner
Partner

I think you're missing quoutes:

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

Highlighted
Contributor III
Contributor III

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.

Highlighted
Partner
Partner

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.

Highlighted
Contributor III
Contributor III

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

Highlighted
Partner
Partner

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

Highlighted
Contributor III
Contributor III

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

Highlighted
Partner
Partner

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

Highlighted
Contributor III
Contributor III

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.

Highlighted
Contributor III
Contributor III

check if this works

 

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