Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2023, a live, in-person thrill ride. Save $300 before February 6: REGISTER NOW!
cancel
Showing results for 
Search instead for 
Did you mean: 
akriaviaan
Contributor III
Contributor III

How to restrict a Date and year values in an expression

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.

1 Solution

Accepted Solutions
sunny_talwar

Check the attached

View solution in original post

11 Replies
sunny_talwar

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

akriaviaan
Contributor III
Contributor III
Author

Hi Sunny,

It is based on fiscal year

sunny_talwar

What is your fiscal year start date?

akriaviaan
Contributor III
Contributor III
Author

Jan 2017 means July 2016

akriaviaan
Contributor III
Contributor III
Author

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)

akriaviaan
Contributor III
Contributor III
Author

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

Check the attached