Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
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

21 Replies
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.

apthansh
Creator
Creator
Author

You are awesome.Than you so much.I appreciate it.

In the new app if you look at , Year and Month are blank if the start date and End date are same.

Ex:Below highlighted should be under 2019 - 2020 FY.Thank yo so much.

 

Test1.PNG

Vegar
MVP
MVP

The month is equivalent to the num of the first day of the month.

Monthname(today()) -> 1/4/2020  //Today is Apr 9 2020.

Your interval Expense start - end does not cover any Month value therefore your will get no match. 

 

(You need to figure out how you want to handle these. How do you want to calculate if you have many single days records within a single month.)

 

 

aditya9243
Contributor
Contributor

You can refer the whole document by henric and search for the topic of slowly changing dimension
apthansh
Creator
Creator
Author

right.. I see the problem here..

I want that to fall under the particular FY calendar that date belongs to and also if the interval(month) is 0 then Amount should be as is, i.e. Amt * 1..

apthansh
Creator
Creator
Author

Also - That is not the case with few of them...in the updated QVW you sent, there are few with same dates, but they have the Year & Month..

 

Test3.PNG

 

Vegar
MVP
MVP

The examples in your latest image are all the first of the month and will match with the month as it is also the numeric value equivalent to the first of its month.

apthansh
Creator
Creator
Author

Ah ok...Got it...Thank you.

Do you have any solution to my issue below ? sorry for the trouble and thank you so much for the help..

I want that to fall under the particular FY calendar that date belongs to and also if the interval(month) is 0 then Amount

should be as is, i.e. Amt * 1..

alex00321
Creator II
Creator II

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

apthansh
Creator
Creator
Author

Thank you Vegar.I appreciate your time and help.You are awesome 🙂