topic Re: Trend Line by Fiscal Years in QlikView

Trend Line by Fiscal Years

triekong — Sat, 16 Nov 2024 03:23:48 GMT

i have 3 fields for Amount calculation, depending on FiscalYear. they are: FiscalYear1Amount, FiscalYear2Amount, and FiscalYear3Amount. These are flag fields, where the calculation for the amount for Fiscal Year 1, is where

sum({<FiscalYear1Amount=1>} Amount)

i would like to see a Trend line by Fiscal years, but i do not have a date field or fiscal year field in my dataset. i am trying to avoid having to use a 'master calendar' for this solution.

Trying to achieve something like this:.thanks!

Re: Trend Line by Fiscal Years

PrashantSangle — Tue, 04 Jun 2019 13:37:05 GMT

the simplest way which come in my mind is
load same table 3 times, every time with one measure field and one static flag field which mention its fiscal year. keep field name same in all 3 tables and concate all 3 tables. then use flag field as dimension and measure field as measure.

Script something like this
Load FiscalYear1Amount as FiscalYearAmount,
'FY1' as fiscal_flag
from tableNAme;
concatenate
Load FiscalYear2Amount as FiscalYearAmount,
'FY2' as fiscal_flag
from tableNAme;
concatenate
Load FiscalYear3Amount as FiscalYearAmount,
'FY3' as fiscal_flag
from tableNAme;

then in line chart
dimesion is > Fiscal_flag
Measure is > Sum(FiscalYearAmount)

Regards,
Prashant Sangle

Re: Trend Line by Fiscal Years

triekong — Tue, 04 Jun 2019 14:09:56 GMT

@PrashantSangle thanks for your fast response!
the problem i have with this one is, from the inline table, that FiscalYearAmount does not take into consideration the flag of 1 for the various fiscal years.
sum({<FiscalYear1Amount=1>} Amount) . the FiscalYear1Amount is a flag field with values '1' or '0'. The amount for the fiscal year 1, is only present when the FiscalYear1Amount field =1.
thanks!

Re: Trend Line by Fiscal Years

PrashantSangle — Wed, 05 Jun 2019 09:38:40 GMT

can you provide sample data?