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
Partner
Partner

How to write a expression

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

                        


4 Replies
Partner
Partner

Re: How to write a expression

Kindly can any one reply me on requirement

Partner
Partner

Re: How to write a expression

Any Suggestions On the requirement

Re: How to write a expression

You posted a Qlik Sense question in a forum called "QlikView Creating Analytics".

To get more feedback from Qlik Sense experts, you may want to move your discussion to an appropriate Qlik Sense forum by following the steps outlined here: QlikCommunity Tip: How to move your discussion thread

Thanks.

Partner
Partner

Re: How to write a expression

Well, I think   Expressions and Scripting is same in both