Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

quriouss
Contributor III

How do I get the most recent posting date for a measure?

I have the following data.  How do I get the most recent month which has posted values in a formula? (Unfortunately it doesn't correspond to the current or prior month in a nice fashion).

  

MonthPosted ValuesPlan Values
1352200
2194200
30200
4170200
5200
6200
Total717800

In English, I want to say, "Give me the sum of the plan values for all the months up to the most recent month with Posted Values".

I can't just use Max (Month), because that will give June.  I tried Max (if ([Posted Values] > 0 , Month , 0)) but that just gave 0.  I think my confusion is that I'm trying to use Month as a measure as well as a dimension for aggregation.

Is this a job for set analysis, or should it be fixed in the load script (and if so, how???).

1 Solution

Accepted Solutions

Re: How do I get the most recent posting date for a measure?

=SUM({<Month = {"=SUM([Posted Values] > 0)"}>}[Plan Values])

6 Replies

Re: How do I get the most recent posting date for a measure?

Try with

Only({$ <[Posted Values]={'$(=Max([Posted Values]))'}>} Month)

let me know

prabhu0505
Valued Contributor

Re: How do I get the most recent posting date for a measure?

=Only({$<Month={$(=Max({$<[Posted Values]-={}>}Month))}>}[Posted Values])

Results: 170

Re: How do I get the most recent posting date for a measure?

=SUM({<Month = {"=SUM([Posted Values] > 0)"}>}[Plan Values])

quriouss
Contributor III

Re: How do I get the most recent posting date for a measure?

Well that certainly seems to work (thanks!).

I still can't totally get my head around Set Analysis, so I'm not totally sure why that should work (I suppose that {<Month = {"=SUM([Posted Values] > 0)"}>} is acting like a WHERE statement.

In any case, I'll bookmark this because I fear it will come in useful in the future.

Re: How do I get the most recent posting date for a measure?

=SUM({<Month = {"=SUM([Posted Values] > 0)"}>}[Plan Values])


Month = {"=SUM([Posted Values] > 0)"}

This will consider on those months where PostedValue is higher than 0...

quriouss
Contributor III

Re: How do I get the most recent posting date for a measure?

A-ha - things are becoming clearer now.  Curly-brackets = "WHERE" (I think I've read something similar elsewhere).

Thanks again.

Community Browser