Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

bharatkishore
Not applicable

Need year start

Hi All,

I am having a field called month and in that the data like Jan-15,Feb-15.....May-16.

Now first thing i needed is I need to get Jan-16..If  i select May-16 i am getting as Jan-16 by writing the following expression

YearStart(S5_Month2) and for monthend i have given like this MonthEnd(S5_Month2)..

Now i need to write an expression like sum(if(S5_Month2>='1/1/2016', if(S5_Month2<='5/1/2016',([S5_Leads])))) without hard coding..

Kindly suggest

Thanks,

Bharat

1 Solution

Accepted Solutions
settu_periasamy
Not applicable

Re: Need year start

Have you tried, what i have given for 2015? it is working for me..

and, do you still need if condition?

Capture.JPG

12 Replies
swuehl
Not applicable

Re: Need year start

Convert your S5_Month2 to a date file using Date#() function, like

LOAD

     S5_Month2,

     Monthstart(Date#(S5_Month2,'MMM-YY')) as S5_Month2_Date,

     ...

Then:

Sum(If( S5_Month2_Date >= Yearstart(Today()) and S5_Month2_Date <= /*how you derived '5/1/2016' */ , [S5_Leads]))

bharatkishore
Not applicable

Re: Need year start

Thanks a lot stefan.. i have almost done but i needed slight help when i selecting the filter of May-16 by giving the following expression as :

YearStart(S5_Month2) then i am getting as Jan-16 but can you pls tell me how can i achieve Jan-15..

Because by hard coding i have written the following expression:

=(sum(if(S5_Month2>='1/1/2016', if(S5_Month2<='5/1/2016',([S5_Leads]))))-sum(if(S5_Month2>='1/1/2015', if(S5_Month2<='5/1/2015',([S5_Leads])))))/sum(if(S5_Month2>='1/1/2015', if(S5_Month2<='5/1/2015',([S5_Leads]))))

Now my requirement is if i select filter of month the values should display according the filter i selected in month.

Suppose if i select Apr in the filter then the expression should be

=(sum(if(S5_Month2>='1/1/2016', if(S5_Month2<='4/1/2016',([S5_Leads]))))-sum(if(S5_Month2>='1/1/2015', if(S5_Month2<='4/1/2015',([S5_Leads])))))/sum(if(S5_Month2>='1/1/2015', if(S5_Month2<='4/1/2015',([S5_Leads]))))


I explained you by hard coding but now i want to remove hard coding.


Kindly suggest..

Please let me know if you require anything more.

swuehl
Not applicable

Re: Need year start

You can use the second argument to yearstart() function to get the previous year:

YearStart(date[, period_no[, first_month_of_year]])


=Yearstart(Today(), -1)

You may need to apply set analysis to be able to access records outside your current selections, though (depending on which field you make active selections and how this field is linked to your S5 fields):

The Magic of Set Analysis - Point In Time Reporting • Blog • AfterSync

bharatkishore
Not applicable

Re: Need year start

I have attached the qvw ... pls have a look and kindly suggest.. i m bit confused.. Pls tell me how can i achieve this..

Many thanks

swuehl
Not applicable

Re: Need year start

Sorry, I am not able to open QVWs at the moment. Maybe someone else can help you with your issue.

bharatkishore
Not applicable

Re: Need year start

No problem..But i have given the following expression as suggested by you..

=Sum(If( S5_Month2 >= Yearstart(Today()) , if(S5_Month2 <=MonthEnd(S5_Month2) , [S5_Leads])))

I am not getting the right total and i m getting just total of the selected month..not all the 5 months total..

settu_periasamy
Not applicable

Re: Need year start

Hi Bharat,

May be like this?

sum({<S5_Month2={">=$(=Date(vMonthStart,'MMM-YY'))<=$(=Date(vMonthEnd,'MMM-YY'))"}>}[S5_Leads])

bharatkishore
Not applicable

Re: Need year start

Hi Settu..

Thank you so much.. this is what i needed for one expression.

And for other expression i need to get Jan-15 to May-15 if i select may-16 filter.. kindly help..

settu_periasamy
Not applicable

Re: Need year start

Try with AddYear(Date,-1)  or AddMonths(Date,-12)

Like

sum({<S5_Month2={">=$(=Date(AddYears(vMonthStart,-1),'MMM-YY'))<=$(=Date(AddYears(vMonthEnd,-1),'MMM-YY'))"}>}[S5_Leads])