Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi There
I am looking for Set analysis or any other way to find current month / previous month and current quarter / previous quarter calculation.
i.e When user selects any date from list box , it should show current month/previous month and current quarter/ previous quarter values.
I am date in below format. cob_date - > 2019-06-28
measure name - "impact"
thanks for your help.
Hi ashishpalkar,
I think You can achieve that by creating two variables i.e one for current month/quarter and one for previous Month/quarter as follows:
1.Variable for current month: vCurrent=Month(Max([ cob_date],'YYYY-MM-DD')) .
2. Variable for Previous Month: vPrevious=Month(Addmonths(Max([cob_date],'YYYY-MM-DD'),-1 )) .
Now create two set expressions one for current month and the other for previous month as follows.
1.Sum({<Month={$(=vCurrent)}>}impact) .
2.Sum({<Month={$(=vPrevious)}>}impact).
thanks for updates, any idea about current quarter and previous quarter?
yeah, just add the following before month in the current and previous variables:
i.e v Current = 'Q' & Ceil(Month(Max([Order Date],'DD-MM-YYYY')/4)) and
v Previous = 'Q' & Ceil(Month(Addmonths(Max([Order Date],'DD-MM-YYYY'),-1 )/4))
Note: I divided the expression by number 4 to get the quarter and you can use any value based upon your business.
You might want to take a look at this example Sense app, that has a number of different prior periods coded for:
Hi , I am getting dates Like below
cob_date - > 2019-06-28
- > 2019-03-28
- > 2019-01-28
In above scenario this expression is not working vPrevious=Month(Addmonths(Max([cob_date],'YYYY-MM-DD'),-1 ))
any suggestions?
Are you saying about the Quarter or Month. Can you just Elaborate it.
I am calculating previous month data. Data I am receiving is not consistent month on month basis.
You are missing the Date function, it needs to sit between the AddMonths and Max functions. The date mask is the second parameter of the Date function.
Steve