topic Re: Count Based on Max Week Ending Date in QlikView

Count Based on Max Week Ending Date

aftabn10 — Mon, 23 Sep 2019 09:55:00 GMT

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.

Re: Count Based on Max Week Ending Date

DavidM — Mon, 23 Sep 2019 11:29:09 GMT

I think you're missing quoutes:

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

Re: Count Based on Max Week Ending Date

aftabn10 — Mon, 23 Sep 2019 11:44:23 GMT

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.

Re: Count Based on Max Week Ending Date

DavidM — Mon, 23 Sep 2019 11:47:08 GMT

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.

Re: Count Based on Max Week Ending Date

aftabn10 — Mon, 23 Sep 2019 12:26:25 GMT

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

Re: Count Based on Max Week Ending Date

DavidM — Mon, 23 Sep 2019 12:27:55 GMT

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

Re: Count Based on Max Week Ending Date

aftabn10 — Mon, 23 Sep 2019 12:31:11 GMT

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

Re: Count Based on Max Week Ending Date

DavidM — Mon, 23 Sep 2019 13:08:34 GMT

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

Re: Count Based on Max Week Ending Date

aftabn10 — Tue, 24 Sep 2019 12:51:31 GMT

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.

Re: Count Based on Max Week Ending Date

qlik_venu — Tue, 24 Sep 2019 13:39:05 GMT

check if this works

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

Re: Count Based on Max Week Ending Date

aftabn10 — Tue, 24 Sep 2019 13:43:02 GMT

Thanks for your reply Qlik_venu, unfortunately tried that but still the same.

Re: Count Based on Max Week Ending Date

aftabn10 — Tue, 24 Sep 2019 14:01:09 GMT

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

Re: Count Based on Max Week Ending Date

johnca — Tue, 24 Sep 2019 14:50:47 GMT

Have you tried single quotes rather than double quotes?

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

And, make sure both fields are formatted the same.