Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2022, LIVE in Denver CO., May 16-19, 2022. REGISTER NOW TO RECEIVE EARLY BIRD PRICING
cancel
Showing results for 
Search instead for 
Did you mean: 
apthansh
Creator
Creator

Fiscal Year expense calculation

I have data as below to calculate the Total Amount.

IDAmount IDAmountStart DateEnd Date
800110855.353/1/2018 0:0010/31/2018 23:59
800211107.811/1/2018 0:0010/31/2019 23:59
800111410.7411/1/2019 0:0010/31/2020 23:59

 

In this FY is from July 1st - June 30th.

Below should be the calculation.

FY for 2019 is July 2018 - June 2019.Based on the start date and End date there are 4 Months in 1st row and 8 months in 2nd row that falls under the 2019 FY.so Expense would be (10,855 * 4) + (11,107*8) = 132,283.

FY for 2020 is July 2019 - June 2020.Based on the start date and End date there are 4 Months in 2nd row and 8 months in 3rd row that falls under the 2020 FY.so Expense would be (11,107*4) + (11,410*8) = 135,717.

I want to show Expense by Year in bar chart.

Thank you much

2 Solutions

Accepted Solutions
Vegar
MVP
MVP

OK, back to the first solution again. The issue is that you have multiple rows with the same interval in the join. I've adjusted the intervalmatch table to only read distinct intervals.

Left join (Trans)
IntervalMatch(Month)
LOAD Distinct
expense_start, expense_end
Resident
Trans;

Then I get the output as in the pictrue below.

image.png

Attaching a new qvw.

Plees ekskuse my Swenglish and or Norweglish spelling misstakes

View solution in original post

alex00321
Creator II
Creator II

Hi I've updated the code a little bit, see if this works for you.

View solution in original post

21 Replies
apthansh
Creator
Creator
Author

@Sunny @sunny_123 @sunny1 @sunnyc129 @swuehl @jagan 

 

You guys are experts...any thoughts on this please ? I appreciate your help.

Vegar
MVP
MVP

You could use intervalmatch() to solve this.

image.png

See attached qvw

Plees ekskuse my Swenglish and or Norweglish spelling misstakes
apthansh
Creator
Creator
Author

 

Question - Why do you have Min and Max date hardcoded.

 

LET _mindate = '3/1/2018';
LET _maxdate = '10/31/2020';

Vegar
MVP
MVP

Because it was quick and easy to do for my demo. I recommend you to dynamically calculate your month values from your source data.

Plees ekskuse my Swenglish and or Norweglish spelling misstakes
apthansh
Creator
Creator
Author

Also - I will be showing data for- 2016 to 2020.

So - I have set min/max date as below.

LET _mindate = '7/01/2016';
LET _maxdate = '6/30/2020';

and added where clause to the source file as below.

where [expense_start] >='7/01/2016' and expense_end <='6/31/2020';

But this did not help.

apthansh
Creator
Creator
Author

Hi - I have attached the sample file that I am testing.Can you please check ID - 749926 ?Some months are double counting.Test.PNG

apthansh
Creator
Creator
Author

Can you please check the attached sample that I am working on ? For some reason Months are duplicating and Expense as well.Thank you so mcuh.

Test.PNG

Vegar
MVP
MVP

I see. There is a misstake done in the join. The join should be on the calendar not the transaction. When joining an non distinct table in to transactions your tranactions will duplicate.  

You will get an synthetic key, but dont worry about it. It is ok to leave an syntethic key when using intervalmatch. If you do not want it then you can remove it after the join by creating a concatenated field of expence start and end. 

LET _mindate = '7/01/2016';
LET _maxdate = '6/30/2020';

Calendar:
LOAD
  YearName(Month,0,7) as Year,
  Month,
;
LOAD 
  MonthName('$(_mindate)',iterno()-1) as Month
autogenerate 1
While 
  MonthName('$(_mindate)',iterno()-1)  < '$(_maxdate)'
;
Trans:
LOAD 
  RowNo() as TransID,
  record_id, 
  expense_amount, 
  date(expense_start) AS expense_start, 
  date(expense_end) AS expense_end
FROM
  expense_schedule.xlsx
  (ooxml, embedded labels, table is expense_schedule)
where 
  date(expense_start,'M/DD/YYYY') >='7/01/2016' and 
  date(expense_end,'M/DD/YYYY') <='6/30/2020';

/*IntervalMatch your Start and End against the Month-value (All three are duals with a numeric value)*/
Left join (Calendar)
IntervalMatch(Month)
LOAD 
  expense_start, 
  expense_end
Resident
  Trans
;

 

Plees ekskuse my Swenglish and or Norweglish spelling misstakes
alex00321
Creator II
Creator II

You are awesome!! I updated the code a little bit to remove the synthetic keys, but I could still not figure out how to summarize the number, seems it's not a total, it's showing each month's value: