topic Re: help with set analysis in QlikView

help with set analysis

Anonymous — Thu, 03 Mar 2016 12:54:22 GMT

Hi i am working on set analysis for lastyear mtd values

i am calculating with variables

below are my variable declaration

for

LastYearMonthStart (vMnthStartLY) : =Date(MonthStart(AddMonths(Max(CalDate),-12)),'DD/MM/YYYY')

LastYearMonthToDate (vLyMtd) :=Date(Max(CalDate)-364,'DD/MM/YYYY')

below is my set Expression

Sum({<CalDate={'>=$(vMnthStartLY) <=$(vLyMtd)'}>}LY_DAY_NET_SALES)

the above set expression is giving zero values between dates in the variable but when i hard code particular dates it is giving correct results .

but my variables used in the set expresiiion are also giving the correct dates .

please fnd the attachment

Any help is appreciated

Thanks

Re: help with set analysis

avinashelite — Thu, 03 Mar 2016 12:59:12 GMT

Try with double Quotes

Sum({<CalDate={">=$(vMnthStartLY) <=$(vLyMtd)"}>}LY_DAY_NET_SALES)

Re: help with set analysis

Peter_Cammaert — Thu, 03 Mar 2016 12:59:36 GMT

Do those variables contain the right-hand expression itself, or do they contain the result of the expression?

Re: help with set analysis

MK_QSL — Thu, 03 Mar 2016 13:06:11 GMT

Create tow variables in Variable Overview as below

vMnthStartLY

Date(MonthStart(Max(CalDate),-12))

vMnthEndLY

Date(MonthEnd(Max(CalDate),-12))

Make sure that in Variable definition you should not use = in front of any Variable expression

Now use below expression

=SUM({<CalYear= , CalMonth = , CalDate = {">=$(=$(vMnthStartLY))<=$(=$(vMnthEndLY))"}>}LY_DAY_NET_SALES)

Re: help with set analysis

settu_periasamy — Thu, 03 Mar 2016 13:07:57 GMT

What about your CalDate format?

For testing, remove the label for set expression and check it is showing correct date value

Re: help with set analysis

Anonymous — Thu, 03 Mar 2016 13:08:00 GMT

variables contain result of the expression .

i have declared as below

vMnthStartLY =Date(MonthStart(AddMonths(Max(CalDate),-12)),'DD/MM/YYYY')

vLyMtd =Date(Max(CalDate)-364,'DD/MM/YYYY')

Re: help with set analysis

Anonymous — Thu, 03 Mar 2016 13:08:26 GMT

I have already tried avinash but no luck

Re: help with set analysis

sunny_talwar — Thu, 03 Mar 2016 13:10:32 GMT

In addition may be try removing the space between your first variable and less than equal sign (<=)

Sum({<CalDate={">=$(vMnthStartLY)<=$(vLyMtd)"}>} LY_DAY_NET_SALES)

Re: help with set analysis

Peter_Cammaert — Thu, 03 Mar 2016 13:11:24 GMT

Can you give an example of their current values? Does the formatting you are using in the Date() call match the default DateFormat setting?

Re: help with set analysis

Anonymous — Thu, 03 Mar 2016 13:13:22 GMT

Hi Settu

i have tested it is giving correct values

Re: help with set analysis

MK_QSL — Thu, 03 Mar 2016 13:27:46 GMT

have you tried my solution?

Re: help with set analysis

avinashelite — Thu, 03 Mar 2016 13:33:26 GMT

could you please share the sample app??

Re: help with set analysis

Anonymous — Thu, 03 Mar 2016 13:47:44 GMT

I have tried your solution but no luck .

The formular which you have given for Date(MonthEnd(Max(CalDate),-12)) is giving monthend date but i need mtd for lastyear MTD.

for Eg: if i select 20/02/206 it should give mtd for the last year i.e 21/02/2015 and should be based on day .20/02/206 is saturday and 21/02/2015 is saturday. so mTd is based on day but not on dates.

we want to compare sales based on day .

I tried with your set expression it is giving highest value.

for eg: i have data for 10th feb 2016 to 20 feb 2016

i have data ly_day_net_sales and it is displayign correct results but i am working for mtd values it is taking sum of all net sales between 10th feb 2015 and 21 feb 2015 (As it is last year sales).

and selection of date is not getting applied on mtd column

Thanks .

Re: help with set analysis

MK_QSL — Thu, 03 Mar 2016 14:03:37 GMT

Change 2nd Variable as below

Date(AddMonths(Max(CalDate),-12))

Rest all as it is as per my previous answer.

EDITED: This will not work as per your requirements.

Re: help with set analysis

MK_QSL — Thu, 03 Mar 2016 14:06:13 GMT

Can you provide sample data please?

Re: help with set analysis

brijesh1991 — Thu, 03 Mar 2016 14:32:51 GMT

Sum({<CalDate={'>=$(=vMnthStartLY) <=$(=vLyMtd)'}>}LY_DAY_NET_SALES)

Re: help with set analysis

MK_QSL — Thu, 03 Mar 2016 14:40:23 GMT

Create tow variables in Variable Overview as below

vMnthStartLY

Date(MonthStart(Max(CalDate),-12))

vMnthDayLY

Date((Max(CalDate)-364))

Make sure that in Variable definition you should not use = in front of any Variable expression

Now use below expression

=SUM({<CalYear= , CalMonth = , CalDate = {">=$(=$(vMnthStartLY))<=$(=$(vMnthDayLY))"}>}LY_DAY_NET_SALES)