Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Welcome to Qlik Community! Check out our new navigation! FIND OUT MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
triekong
Creator
Creator

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

Labels (5)
3 Replies
PrashantSangle

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
Creator
Creator
Author

@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!
PrashantSangle

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