How to restrict a Date and year values in an expression

[Anonymous]

Hi All,

I want to calculate a KPI for  ...  Sum(Value).

The required o/p is

1)Sum of Value for entire Current year and upto previous month

=num(sum({<CalendarDate={'<=$(v_MaxMonth)'},AC_FiscalYear={'2017'}>}Value),'#,##0')

  2)Sum of Value for Current year previous month.

=sum({<FiscalYear={'2017'},CalendarDate  = {$(= Max([CalendarDate (#)]), -1)},CalendarDate=>}Value)

I used the above formulas are not working.

Cann any one help on this.

[sunny_talwar]

Is this calculation based on fiscal year or calendar year? Would you be able to share a sample?

[Anonymous]

Hi Sunny,

It is based on fiscal year

[sunny_talwar]

What is your fiscal year start date?

[Anonymous]

Jan 2017 means July 2016

[Anonymous]

can you let me know ,What will be the logic for Calendar Year and Calendar date?

[sunny_talwar]

May be like this:

Sum({<CalendarDate = {"$(='>=' & Date(YearStart(Max(CalendarDate), 0, 6), 'DateFieldFormatHere') & '<' & Date(MonthStart(Max(CalendarDate)), 'DateFieldFormatHere'))"}, FiscalYear, CalendarYear>} Value)

[Anonymous]

Hi Sunny,

Can you please have check the attached qvw.

[sunny_talwar]

What is the expected output (numerical output) for the two text boxes?

[sunny_talwar · Accepted solution]

Check the attached