topic Re: Need year start in QlikView

Need year start

bharatkishore — Mon, 05 Sep 2016 17:10:47 GMT

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

Re: Need year start

swuehl — Mon, 05 Sep 2016 17:29:54 GMT

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]))

Re: Need year start

bharatkishore — Mon, 05 Sep 2016 17:45:56 GMT

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.

Re: Need year start

swuehl — Mon, 05 Sep 2016 17:54:09 GMT

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

Re: Need year start

bharatkishore — Mon, 05 Sep 2016 18:00:01 GMT

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

Re: Need year start

swuehl — Mon, 05 Sep 2016 18:01:43 GMT

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

Re: Need year start

bharatkishore — Mon, 05 Sep 2016 18:09:11 GMT

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

Re: Need year start

settu_periasamy — Mon, 05 Sep 2016 18:33:25 GMT

Hi Bharat,

May be like this?

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

Re: Need year start

bharatkishore — Mon, 05 Sep 2016 18:39:44 GMT

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

Re: Need year start

settu_periasamy — Mon, 05 Sep 2016 18:45:48 GMT

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

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

Re: Need year start

bharatkishore — Mon, 05 Sep 2016 18:50:39 GMT

I have given the following hard coding 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]))))

With your help i have completed the first expression i.e.

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

Now i need to achieve the second expression i.e.

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

If i give add months and in the filter if i select May-2016 it is showing as May-15 but i need to get Jan-15 if i select May-2016.

Re: Need year start

settu_periasamy — Mon, 05 Sep 2016 18:57:18 GMT

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

and, do you still need if condition?

Re: Need year start

bharatkishore — Mon, 05 Sep 2016 19:03:16 GMT

Thanks a lot...Sorry My bad didn't check... thank u so much...