Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Graph with data Dec over different years + MTD for current year

Hi all,

I'm quite stuck in my set analysis for the following:

I want to count the nrs of account we have in the month december of eacht year since 2014. For the current year, I want to have the nrs for the last month.

I have tried different set-ups but none work.

I have identified the max month per year throught following formula:

Max({<IsActual_={1}>} Month)

Combined with year it gives me the below as expected

However, when I try combining this into the count of my accounts, it doesn't work.

count( {<[Month] = {"$(=Max({<IsActual_={1}>}[Month]))"}> }distinct [DMU])

I guess because Max({<IsActual_={1}>}[Month] is giving a numeric result (12 or 7) while my field Month is giving 'Dec' or 'Aug' as result.

Then I tried

if ( 

Year = Year(Today()),

count( {< Month = {'Month(Today())'} > } distinct [DMU]),

Count( {< Month = {'Dec'} >} distinct [DMU])

)

this gives me all values but for 2017

help is welcome ...

13 Replies
sunny_talwar

So do you want July's data or Aug's data?

Not applicable
Author

you're right, confusing. sorry. It'll be better to have last month's data. so july in our case now (but agust for next month)

sunny_talwar

What about this

RangeSum(

Count({<Month = {'$(=Month(AddMonths(Today(), -1)))'}, Year = {$(=Year(Today()))}>} DISTINCT [DMU]),

Count({<Month = {'Dec'}, Year -= {$(=Year(Today()))}>} DISTINCT [DMU])

)

Not applicable
Author

yes!! it works perfectly! Thanks a lot