Quarter Calculations

[dia2021]

Hi All,

Could you please help me with the syntax? I'm trying to subtract the current max month Quarter Value from the Previous max month Quarter value.

I have created two variables. 

Let vCurrentQtr = 'Q' & ceil(month(today()) / 3)&'-' & Year(today());
Let vPreviousQtr = 'Q' & ceil(month(addMonths(today(),-3)) / 3)&'-' & Year(addMonths(today(),-3));

but while set analysis at the front end using this formula gives me an error 

sum({<Date={$(vCurrentQtr)}>}budhrs)-sum({<QuarterYear={$(vPreviousQtr)}>}budhrs)

Please see attached screenshot for an error. Can anyone help me fixing this syntax? Thanks in advance!

[MayilVahanan]

Hi 

Try like below

sum({<Date={'$(vCurrentQtr)'}>}budhrs)-sum({<QuarterYear={'$(vPreviousQtr)'}>}budhrs)

[PrashantSangle]

You are missing single quote in set analysis. 

Two way to resolve it

1: suggested by Mayil

2: in variable definition, you can add single quote

like below

Let vCurrentQtr = chr(39)&'Q' & ceil(month(today()) / 3)&'-' & Year(today())&chr(39);
Let vPreviousQtr = chr(39)&'Q' & ceil(month(addMonths(today(),-3)) / 3)&'-' & Year(addMonths(today(),-3))chr(39);

& then use your expression.

Regards,

Prashant Sangle 

[dia2021]

@MayilVahanan @PrashantSangle Thanks for the quick response! 

I tried the way you both have suggested; however, it's still giving the same error. 

[PrashantSangle]

Hi,

Can you share the snip of error after both solution applied.

Let vCurrentQtr = chr(39)&'Q' & ceil(month(today()) / 3)&'-' & Year(today())&chr(39);
Let vPreviousQtr = chr(39)&'Q' & ceil(month(addMonths(today(),-3)) / 3)&'-' & Year(addMonths(today(),-3))&chr(39);

I missed & in 2nd variable. also if possible share app to exact solution. try double quote instead of single quote in mayil solution.

Regards,

Prashant Sangle

[dia2021]

Hi Prashant, 

Thank you for helping! The syntax worked with the double-quotes. Now, the final result as output is 0. Could you please suggest to me how I can fix this?

[dia2021]

@PrashantSangle I found the issue. I have separated the calc to narrow down the problem. I noticed that the current and Previous quarter is reading the same value. Hence the difference is 0. Any suggestions on how to fix this??

[PrashantSangle]

select those quarter in front end check the values are coming properly or not.

[Kushal_Chawda · Accepted solution]

@dia2021  If you have Date field and your Date field is in proper date format  then try below

sum({<Date={">=$(=quarterstart(today(1)))<=$(=quarterend(today(1)))"}>}budhrs)-sum({<Date={">=$(=quarterstart(today(1),-1))<=$(=quarterend(today(1),-1))"}>}budhrs)

[dia2021]

It's not working.