Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
Not applicable

Problem with Max Date and Set Analysis

Hi all,

I'm trying to sum all of the numbers in a field for a specific month. This should be easy with set analysis, but I'm having difficulty. The field is called "PoolingMOUAllocation" and the specific month is whatever is the most recent month (and year - but that part is figured out) available. For my data, that month is is May. My problem is that I have my month data stored in a field called BillMonth, which was created at load time using the Month() function - this function stores the value as the string version of the month (to my knowledge). When I compare values in set analysis, I use the max() funciton, which returns a num variable. I have tried a couple expressions below:

'Current Pool Minutes: ' & num(sum({<BillMonth = {$(=Max(BillMonth))}}>}PoolingMOUAllocation), '###,###.')

'Current Pool Minutes: ' & num(sum({<MonthNumber = {$(=Max(BillMonth))}>}PoolingMOUAllocation), '###,###.')

FYI - "MonthNumber" is a field I created via inline load to map month names to month numbers:

MonthMapping:

LOAD * INLINE [

BillMonth, MonthNumber

January, 1

February, 2

March,3

April, 4

...

Both of the above expressions return a value of 0. Any ideas?

-Paul

2 Replies
Not applicable

Re: Problem with Max Date and Set Analysis

I normally use a date field in order to play with time in set analysis,  something like " Month(Max(DateField)) ", but if you only have the Month field... have your tried using MaxString instead of Max?

Not applicable

Re: Problem with Max Date and Set Analysis

Sorry, nevermind - I actually figured out a solution. Since Month() stores a dual value (both text and num), I created a new field at load time called BillMonthNum where I take num(month(Original_Input_Date)).

This allows me to compare numerical values in set analysis using the max() function.

Thanks!