topic Re: need to understand expression in QlikView

need to understand expression

Anonymous — Wed, 07 Oct 2015 04:20:25 GMT

Hi All,

Can anybody tell me what is below expression trying to do and difference of them.

SUM({$<FinancialYear=,FiscalYear={$(=max(FiscalYear))},

Date={">=$(=YearStart(max({$<FiscalYear={$(=max(FiscalYear))}>}Date),0,4))<=$(=max({$<FiscalYear={$(=max(FiscalYear))}>}Date))"}>}Values )/10000000

---------------------------------------------------------------------------------------------------------------

Sum({$<FinancialYear=,FiscalYear={$(=max(FiscalYear)-1)}

,Date={">=$(=YearStart(max({$<FiscalYear={$(=max(FiscalYear))}>}Date),-1,4))<=$(=addmonths(max({$<FiscalYear={$(=max(FiscalYear))}>}Date),-12))"}

>}Values )/10000000

----------------------------

Sum({$<FinancialYear=,FiscalYear={$(=max(FiscalYear)-1)}>}Values )/10000000

Thanks

Re: need to understand expression

sunny_talwar — Wed, 07 Oct 2015 04:24:10 GMT

First expression is summing the Values for max year between the fiscal year start (which starts from April) to the most recent date. So basically YTD for the most recent year.

Second expressions seems to be summing the YTD for the last fiscal year

Third expression is summing the Values for whole of the last fiscal year (not just the YTD)

Re: need to understand expression

Anonymous — Wed, 07 Oct 2015 04:31:08 GMT

Hi sunindia,

Thanks for your quick response I am getting to much difficulties to understand this complex expression or you can say i am not able to understand qlikview functionality for this what should i do.

Can you please guide me how to understand this types of logic in better way.

Can you please simplify my problem.

I want to become expert like you.

Many Many Thanks

Re: need to understand expression

sunny_talwar — Wed, 07 Oct 2015 05:51:46 GMT

=Sum({$<FinancialYear=,FiscalYear={$(=max(FiscalYear))},

Date={">=$(=YearStart(max({$<FiscalYear={$(=max(FiscalYear))}>}Date),0,4))<=$(=max({$<FiscalYear={$(=max(FiscalYear))}>}Date))"}>}Values )/10000000

Lets just start with this first expression, may be once you understand this, you will have a better understanding of how the other expressions are working.

FinancialYear=

This part means that ignore any selections made in FinancialYear list box (your expression won't change on any selections made in FinancialYear list box)

FiscalYear={$(=max(FiscalYear))}

This part means that look at only the FiscalYear where it equals the Max(FiscalYear). So for instance in your database you have a max fiscal year of 2015 then this expression will only display the result for 2015 fiscal year.

Date={">=$(=YearStart(max({$<FiscalYear={$(=max(FiscalYear))}>}Date),0,4))<=$(=max({$<FiscalYear={$(=max(FiscalYear))}>}Date))"}

Lets break this one down into two pieces:

>=$(=YearStart(max({$<FiscalYear={$(=max(FiscalYear))}>}Date),0,4))

This part is Looking at max(Date) -> max({$<FiscalYear={$(=max(FiscalYear))}>}Date) but only for the most recent fiscal year -> FiscalYear={$(=max(FiscalYear))}

So lets say you have the max date of 12/31/2015 then this -> max({$<FiscalYear={$(=max(FiscalYear))}>}Date) = 12/31/2015, but since you also have YearStart around the max function and you are specifying that 4 (april) is your start year your date changes to 04/01/2015.

>=$(=YearStart(max({$<FiscalYear={$(=max(FiscalYear))}>}Date),0,4)) will equal >= 04/01/2015

<=$(=max({$<FiscalYear={$(=max(FiscalYear))}>}Date))"}

This part is simple as it is the same as above but without the yearstart so this expression will give you <= 12/31/2015

Rest of the things are pretty straight forward where you are simply summing the Values with the above filters.

HTH

Best,

Sunny

Re: need to understand expression

Anonymous — Wed, 07 Oct 2015 06:39:15 GMT

Hi sunindia‌

Thanks for giving explanation in simple way.

Can you please tell me how to increase development skills in Qlikview.

Or How to become expert in Qlikview

Waiting for your reply...

Thanks

Re: need to understand expression

sunny_talwar — Wed, 07 Oct 2015 15:19:26 GMT

Hi Vijay -

I wish there was a magical potion that we can use to learn QlikView, but unfortunately there is nothing like that available. You learn as you do new things and over the course of few years you will find yourself more confident.

One thing I can suggest is to be very pro-active on the community, the more you participate, the faster you will learn. I have been on QlikView just over a year now, and you will find me very actively participating in as many discussions as I can. I am not perfect and still learning and I definitely make a lot of mistakes, but the best part is, I learnt the most by making those mistakes.

I hope I will actively see your name on the community going forward.

Best,

Sunny