Set analysis: forecst where YearMonth > year(today())&month(today())

[RobinB1]

Hi,

New to qlikview,

I have a field named YearMonth. It's set up so that months jan - sep have a zero before their number, so we have e.g. 202109, 202110...

I've been working on a dynamic expression that would return consumption forecast data where YearMonth is larger than the year&month of today's date.

For example, this one returns data for months 5-12 for all year in scope:

=Sum({<Month={'>$(=Num(Month(Today())))'},>}kWh)/Volume

This one returns data for all months and all years in scope:

=Sum({<Month={'>$(=Month(Today()))'},>}kWh)/Volume

Not sure why. My understanding is that stuff quoted with single quote signs allows us to use QV built-in functions, i.e. the below is not doable since the platform does not include a YearMonth function?

=Sum({<YearMonth={'>$(YearMonth(Today(),"YYYYMMDD"))'},>}kWh)/Volume

I guess set expression modelling fundamentally differs, but borrowing some excel syntax, I'm looking for:

=Sum({<YearMonth={'>$(=Year(Today())&IF(Len(Month(Today()))=1;”0”&Month(Today());Month(today())))'},>}kWh)/Volume

Cheers,

Robin

[jwjackso · Accepted solution]

Try Sum({<YearMonth={">$(=Date (Today (1),'YYYYMM'))"}>}kWh)/Volume

The Date(Today(1),'YYYYMM") should return YearMonth like 202110

[RobinB1]

Thanks, did the trick!