Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

triekong
Contributor

Trend Line by Fiscal Years

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!

Capture12.PNG

3 Replies

Re: Trend Line by Fiscal Years

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
Great dreamer's dreams never fulfilled, they are always transcended.
triekong
Contributor

Re: Trend Line by Fiscal Years

@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

can you provide sample data?
Great dreamer's dreams never fulfilled, they are always transcended.