Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Announcements
Announcing the newest addition to the Qlik Community, Qlik Gallery! Learn More
kunkumnaveen
Valued Contributor

How to get previous fiscal Year max valid from date price

Hello All,

I need to show max valid from of each fiscal year irrespective of selected fiscal year

So in a line chart i took


Dimension :Fiscal Year


Expression:


Avg({<[FISCAL YEAR]>} Aggr(If(Only({<[FISCAL YEAR]>}BFVALIDFROM) = Max({<[FISCAL YEAR]>} TOTAL <[FISCAL YEAR], PROD_HIER4_Name>BFVALIDFROM), Avg({<[FISCAL YEAR]>}BF_PRICE_MAT)),[FISCAL YEAR],BFVALIDFROM,PROD_HIER4_Name)

)

Which is Working Great


Capture.PNG


But i just find out the above expression will work  fine only if it has  Valid From date  in each fiscal year.

In above case Fiscal year 2017 is showing no value , because there is no valid From  in Fiscal year -2017,but according  to the data

fiscal year -2017 should have price 2043.91 Fiscal year 2016 price ,

because the max Valid From of Fiscal Year -2016 is from

                      Valid From              Valid To                        Price                        FY

Capture.PNG

So logically i need to show 2043.91 in FY17 as Well, but my current wont work in that way.

What i was thinking is if in any  Fiscal year, if there is no valid From date value then it has to pick last fiscal year max Valid from date price. !

but i have no clue how to write this expression,

Kindly help me in achieving this requirement


PFA sample data

Tags (1)