topic Re: Fiscal Year expense calculation in QlikView

Fiscal Year expense calculation

apthansh — Sat, 16 Nov 2024 18:49:12 GMT

I have data as below to calculate the Total Amount.

ID	Amount ID	Amount	Start Date	End Date
800	1	10855.35	3/1/2018 0:00	10/31/2018 23:59
800	2	11107.8	11/1/2018 0:00	10/31/2019 23:59
800	1	11410.74	11/1/2019 0:00	10/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

Re: Fiscal Year expense calculation

apthansh — Tue, 07 Apr 2020 20:37:29 GMT

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

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

Re: Fiscal Year expense calculation

Vegar — Wed, 08 Apr 2020 09:49:36 GMT

You could use intervalmatch() to solve this.

See attached qvw

Re: Fiscal Year expense calculation

apthansh — Wed, 08 Apr 2020 12:30:59 GMT

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

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

Re: Fiscal Year expense calculation

Vegar — Wed, 08 Apr 2020 12:53:48 GMT

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

Re: Fiscal Year expense calculation

apthansh — Wed, 08 Apr 2020 14:54:39 GMT

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.

Re: Fiscal Year expense calculation

apthansh — Wed, 08 Apr 2020 16:50:47 GMT

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

Re: Fiscal Year expense calculation

apthansh — Wed, 08 Apr 2020 16:57:01 GMT

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.

Re: Fiscal Year expense calculation

Vegar — Thu, 09 Apr 2020 06:43:34 GMT

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 ;

Re: Fiscal Year expense calculation

alex00321 — Thu, 09 Apr 2020 08:11:56 GMT

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:

Re: Fiscal Year expense calculation

Vegar — Thu, 09 Apr 2020 10:59:12 GMT

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.

Attaching a new qvw.

Re: Fiscal Year expense calculation

apthansh — Thu, 09 Apr 2020 13:53:54 GMT

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.

Re: Fiscal Year expense calculation

Vegar — Thu, 09 Apr 2020 14:03:49 GMT

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.)

Re: Fiscal Year expense calculation

aditya9243 — Thu, 09 Apr 2020 14:50:39 GMT

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

Re: Fiscal Year expense calculation

apthansh — Thu, 09 Apr 2020 15:35:52 GMT

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

Re: Fiscal Year expense calculation

apthansh — Thu, 09 Apr 2020 15:48:19 GMT

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

Re: Fiscal Year expense calculation

Vegar — Thu, 09 Apr 2020 20:02:53 GMT

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.

Re: Fiscal Year expense calculation

apthansh — Thu, 09 Apr 2020 21:31:11 GMT

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

Re: Fiscal Year expense calculation

alex00321 — Fri, 10 Apr 2020 03:00:52 GMT

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

Re: Fiscal Year expense calculation

apthansh — Fri, 10 Apr 2020 18:07:31 GMT

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