Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I am using the following expression in a KPI Control for Qlik Sense:
Sum({$<accountName={"CustName"}, dimDate = {'$(=Max(dimDate))'}>}itemCount)
The dimDate is a column that contains all dates for the past year in YYYY-MM-DD format. In this case I would like to return the itemCount(numeric value) only for the specified customer name, AND for the most recent dimDate please.
Any advise on how to fix my expression will be appreciated.
Thank you!
Try this:
Sum({$<accountName={"CustName"}, dimDate = {"$(=Date(Max(dimDate), 'YYYY-MM-DD'))"}>} itemCount)
Worked really well! THANK YOU!!!
Do you know if there are any demo visualizations with date formatting that I could review? I'd love to see samples of this in other business scenarios.
You saved my life here!
Hi,
Check Qlik help file it has all the details regarding this
From Qlikview Help file:
In number format controls and in several of the interpretation and formatting functions it is possible to set the format for numbers and dates by using a format code. This describes how to format a number, date, time or time stamp.
The format code is used for defining the positions of the separators. It is not possible to set the separator in the format code. Use the respective control (in dialogs) or parameter (in script functions) for this.
It is possible to use the thousand separator to group digits by any number of positions, for example, a format string of "0000-0000-0000" (thousand separator="-") could be used to display a twelve-digit part number as "0012-4567-8912".
QlikView can interpret and format numbers in any radix between 2 and 36 including binary, octal and hexadecimal. It can also handle roman formats.
To indicate binary format the format code should start with (bin) or (BIN).
To indicate octal format the format code should start with (oct) or (OCT).
To indicate binary format the format code should start with (hex) or (HEX). If the capitalized version is used A-F will be used for formatting (e.g. 14FA). The non-capitalized version will result in formatting with a-f (e.g. 14fa). Interpretation will work for both variants regardless of the capitalization of the format code.
The use of (dec) or (DEC) to indicate decimal format is permitted but unnecessary.
To indicate a format in any radix between 2 and 36 the format code should start with (rxx) or (Rxx) where xx is the two-digit number denoting the radix to be used. If the capitalized R is used letters in radices above 10 will be capitalized when QlikView is formatting (e.g. 14FA). The non-capitalized r will result in formatting with non-capital letters (e.g. 14fa). Interpretation will work for both variants regardless of the capitalization of the format code. Note that (r02) is the equivalent of (bin), (R16) is the equivalent of (HEX) and so on.
To indicate roman numbers the format code should start with (rom) or (ROM). If the capitalized version is used capital letters will be used for formatting (e.g. MMXVI). The non-capitalized version will result in formatting with lower cap letters (mmxvi). Interpretation will work for both variants regardless of the capitalization of the format code. Roman numbers are generalized with minus sign for negative numbers and 0 for zero. Decimals are ignored with roman formatting.
The same notation as that of dates and times above is used.
Examples (Numbers):
# ##0 describes the number as an integer with a thousands separator.
###0 describes the number as an integer without a thousands separator.
0000 describes the number as an integer with at least four digits. E.g. the number 123 will be shown as 0123.
0.000 describes the number with three decimals.
0.0## describes the number with at least 1 decimal and at most three decimals.
Examples (Special Number Formats):
num(199, '(bin)') | returns | 11000111 |
num(199, '(oct)') | returns | 307 |
num(199, '(hex)') | returns | c7 |
num(199, '(HEX)' ) | returns | C7 |
num(199, '(r02)' ) | returns | 11000111 |
num(199, '(r16)') | returns | c7 |
num(199, '(R16)' ) | returns | C7 |
num(199, '(R36)') | returns | 5J |
num(199, '(rom)') | returns | cxcix |
num(199, '(ROM)' ) | returns | CXCIX |
Examples (Date):
YY-MM-DD describes the date as 01-03-31.
YYYY-MM-DD describes the date as 2001-03-31.
YYYY-MMM-DD describes the date as 2001-Mar-31.
31 MMMM YYYY describes the date as 31 March 2001.
M/D/YY describes the date as 3/31/01.
W YY-MM-DD describes the date as 6 01-03-31.
WWW YY-MM-DD describes the date as Sat 01-03-31.
WWWW YY-MM-DD describes the date as Saturday 01-03-31.
Examples (Time):
hh:mm describes the time as 18:30
hh.mm.ss.ff describes the time as 18.30.00.00
hh:mm:tt describes the time as 06:30:pm
Examples (Timestamps):
YY-MM-DD hh:mm describes the timestamp as 97-03-31 18:30
M/D/Y hh.mm.ss.ffff describes the timestamp as 3/31/97 18.30.00.0000
In each case this type of thing is done in some sort of aggregation. Can you also restrict a dimension with a condition? For instance I might only want to show customer names where the accountCreateDate is greater than 30 days ago.
Thank you again!