Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Highlighted
greend21
Contributor II

Max Date per Month

I have data like the below example and want my pivot table to only show the values for the max date per month. I have a variable to toggle the view so I tried =If($(vValuationDate) = 0, MonthName(Aggr(Max(ValuationDate), ValuationDate.autoCalendar.YearMonth)), ValuationDate) but it shows the values for 2015-01-22 and 2015-02-12. The other odd thing is if I remove the IF statement and only have =MonthName(Aggr(Max(ValuationDate), ValuationDate.autoCalendar.YearMonth)) I get the sum total for each month instead of a specific week's total. I would think the results would at least be consistent. Does anyone know how to get the desired format?

 Current format:

ValuationDateNumber
2015-01-02131
2015-01-08148
2015-01-15234
2015-01-22233
2015-01-29165
2015-02-05181
2015-02-1272
2015-02-19194
2015-02-2774

 

Needed format:

ValuationDateNumber
2015-01-29165
2015-02-2774
12 Replies

Re: Max Date per Month

May be try something like this

Sum(Aggr(If([Valuation Date] = Max(TOTAL <[ValuationDate.autoCalendar.YearMonth]> Valuation Date), Sum(Number)), [Valuation Date], ValuationDate.autoCalendar.YearMonth))
greend21
Contributor II

Re: Max Date per Month

My original thought was using a calculated dimension to show the specific date thinking the measures would sum for those dates but that doesn't seem to be working. I tried what you provided as a measure but it did not work but I will try a few things based off a similar method.

Re: Max Date per Month

What all do you have dimensions? Do you only have Valuation Date as a dimension or do you have others as well? Also, this is minor, but is your Date field ValuationDate or [Valuation Date] (with a space)... make sure you use the right field name throughout the below expression....

Sum(Aggr(If([Valuation Date] = Max(TOTAL <[ValuationDate.autoCalendar.YearMonth]> Valuation Date), Sum(Number)), [Valuation Date], ValuationDate.autoCalendar.YearMonth))

 

greend21
Contributor II

Re: Max Date per Month

The row dimensions are LOB, BusinessType, and ValuationDate. The column dimension is AgeBucket.

Re: Max Date per Month

Oh so you have a lot more going on then you posted here... would you be able to share  a sample to show what you have and may be share the expected output from the sample you share in an Excel file?

greend21
Contributor II

Re: Max Date per Month

I'm not sure that I'll be able to post an example, but basically my information is like what I posted above. I can get the pivot to display only 2015-01-29 for the month of January but the expression is summing everything when I only want it to display the sum for that last week in January, or 165.

Re: Max Date per Month

I want to help, but without seeing the whole picture, it would be difficult to offer much help.

I hope someone else is able to help you here.

Best,
Sunny
greend21
Contributor II

Re: Max Date per Month

No problem. I was trying to use Aggr to get the sum of ClosedCt per the last week of the month but I can't get it right if it is possible.

Re: Max Date per Month

okay 🙂