topic Re: repeating a value by fiscal year in App Development

repeating a value by fiscal year

ali_hijazi — Fri, 27 Dec 2024 08:27:28 GMT

Hello
I have in my dashboard two dimensnions (fiscal year and month)
I have a calculation to make which is the total attrition budget which is on the latest available month of the fiscal year
My fiscal year starts on October
I managed to get the number per fiscal as shown below; but I need to repeat this value on all months of the fiscal year:
so I want to repeat the -198 from 2023-10 till 2024-09 and repeat the -20 from 2024-10 till today 202412

Re: repeating a value by fiscal year

brunobertels — Fri, 27 Dec 2024 09:08:44 GMT

Not sure but it seems you need to add "nodistinct" in your mesure Sum(Aggr(NODISTINCT sum( etc...

Re: repeating a value by fiscal year

diegozecchini — Fri, 27 Dec 2024 09:57:11 GMT

The suggestion to use NODISTINCT in the Aggr() function is valid if you need the measure to ignore distinctions between dimension values and apply the same calculation consistently across all rows of your dimensions.

Re: repeating a value by fiscal year

eddyvargas — Fri, 27 Dec 2024 13:10:01 GMT

You can create a Calendar in your script to create Fiscal Year, I have the next calendar when I have MAT Period, MAT is the last 12 months of the selected month, for example MAT "September 24" is October 23 to September 24. You can adapt it to your needs

https://github.com/EddyVargas/QlikEdd/blob/main/Audit_Calendar.qvs

Re: repeating a value by fiscal year

ali_hijazi — Fri, 27 Dec 2024 14:14:25 GMT

the nodistinct is acting like {1} ignoring all selections no?
this is not what I want
I want the value that is per fiscal year to be repeated on all fiscal month of that fiscal year
so the value -198 is repeated from 202310 till 202409
then the value -20 is reeated from 202410 till today

Re: repeating a value by fiscal year

Kushal_Chawda — Fri, 27 Dec 2024 15:11:45 GMT

@ali_hijazi Use Total qualifier

= sum(total <[Fiscal Year]> aggr(......

Re: repeating a value by fiscal year

diegozecchini — Fri, 27 Dec 2024 17:43:04 GMT

Total qualifier should works 👍

Re: repeating a value by fiscal year

ali_hijazi — Fri, 27 Dec 2024 18:25:52 GMT

When you put total<fiscal_year> and fiscal year is not a dimension in the chart then fiscal_year is ignored, it works only if fiscal_year is used as a dimension in the chart

And thus it is like putting total so the total is repeated on all months of the two fiscal years

While I want to repeat the value -198 then -20 and not the total which is -218

Please read carefully my question above

Re: repeating a value by fiscal year

ali_hijazi — Fri, 27 Dec 2024 18:27:40 GMT

When you put total it is the total that is repeated on all months of the two fiscal years -218

While I want to repeat the value -198 on the months of the first fiscal year then -20 on the months of the second fiscal year and not the total which is -218

Please read carefully my question above

Re: repeating a value by fiscal year

ali_hijazi — Mon, 30 Dec 2024 15:28:01 GMT

I already have this fiscal calendar
but I'm not able to accomplish what I need because the calculation is aggregated by fiscal year to be corrected but I want to repeat it accross all fiscal months
I can use total but when I have 2 fiscal years selected I want to repeat 2 different values across the months of each fiscal year
using total will repeat the sum of values by fiscal year

Re: repeating a value by fiscal year

eddyvargas — Mon, 30 Dec 2024 20:59:10 GMT

You need to create new fields

Example:

-create Fiscal Year and use Total <Fiscal Year>

-create "Fiscal Year Month" Field that is group of year months

Re: repeating a value by fiscal year

ali_hijazi — Mon, 30 Dec 2024 21:06:14 GMT

Are you aware of the fact that using total<fiscal year> will ignore fiscal year if it is not a dimension in the chart?