Date field creation

[Skolluru78]

Hi, 

I have a year field which is 2022

i have a month field which is Jan, Feb, Mar.......Dec

Now I want a make a date field using year and month field.

Also I want to automate the field in my calculation like it should automatically pick up the all the months until current month

the calculation is like below

Sum({<,[Year]={'2022'},[Month_Pivoted]={'Jan','Feb','Mar','Apr','May','Jun','Jul'}>}Month_Expenditure)/1000000

I want my calculation to automaticall pick the months based on the current month? 

Please advise

[marcus_sommer · Accepted solution]

I suggest to do the essential work already within the data-model, for example with:

date(date#(Month&Year, 'MMMYYYY')) as Date

you could create a real date which could be linked to a master-calendar which could contain all needed period-information including also flags for all kinds of YTD, MTD and so on - in your case maybe:

if(year(Date) = year(today()) and Date <= monthstart(today()) - 1, 1, 0) as Flag

and within the UI then something like:

sum({< Flag = {1}>} Value)

- Marcus

[Skolluru78]

Thanks a lot Marcus for this solution. It really helped me.

[Skolluru78]

HI Marcus,

Hope you are doing great.

I used the above solution and it worked. But when I using the above syntax and modified to get the current month and last month, it is not working. Can you please let me know what i am doing wrong?

if(year(date(date#(Month_Pivoted&Year, 'MMMYYYY'))) = year(today()) and
date(date#(Month_Pivoted&Year, 'MMMYYYY')) <= monthstart(today()) -1, 1, 0) as YTD, (this is working)

if(year(date(date#(Month_Pivoted&Year, 'MMMYYYY'))) = year(today()) and
date(date#(Month_Pivoted&Year, 'MMMYYYY')) > monthstart(today()), 1, 0) as Futuremonths, not working

if(date(date#(Month_Pivoted&Year, 'MMMYYYY')) = monthstart(today()) -1, 1, 0) as Prevmonth,- not working
if(date(date#(Month_Pivoted&Year, 'MMMYYYY')) = monthstart(today()), 1, 0) as Currmonth - not working

Thanks and appreciate your help on the above.

Sowjanya

[marcus_sommer]

You may try it in this way:

if(date#(Month_Pivoted&Year, 'MMMYYYY') > floor(monthstart(today())), 1, 0) as Futuremonths, 

if(num(date#(Month_Pivoted&Year, 'MMMYYYY')) = floor(monthstart(today(), -1)), 1, 0) as Prevmonth,
if(num(date#(Month_Pivoted&Year, 'MMMYYYY')) = floor(monthstart(today())), 1, 0) as Currmonth

because the year-comparing for the future isn't needed, monthstart() didn't return a date else a timestamp which is rounded per floor() and by the previous month the -1 belonged to monthstart() and not reducing this result. 

- Marcus