topic Re: Issue in expression with set analysis in QlikView

Issue in expression with set analysis

Anonymous — Wed, 18 Jan 2017 13:03:04 GMT

Dear All

I have created 2 variables as below:

vStart =date(MonthStart( AddMonths(Max(TDATE),-6)))

vEnd =date(MonthEnd( AddMonths(Max(TDATE),-1)))

I am trying to use these variables to calculate last 6 months data in below expression. However it does not calculate.

It gives the result of the month selected.

=sum( {<TDATE={">=$(=vStart )< $(=vEnd)"}>}Sales)

Do I need to change any format of date or do any modifications in above expression or variable definition?

Thanks & Regards

Chintan

Re: Issue in expression with set analysis

swuehl — Wed, 18 Jan 2017 13:17:06 GMT

If your variable definition already contains the leading equal sign, try

=sum( {<TDATE={">=$(vStart )< $(vEnd)"}>} Sales)

Dates in Set Analysis

Re: Issue in expression with set analysis

sunny_talwar — Wed, 18 Jan 2017 13:17:47 GMT

May be ignore selection in Month field

=Sum({<TDATE={">=$(=vStart )< $(=vEnd)"}, MonthField>}Sales)

Re: Issue in expression with set analysis

Anonymous — Wed, 18 Jan 2017 13:26:33 GMT

Yes correct, tried removing the '='. However it does not take the range. It shows sales amount for the month selected.

Thanks & Regards

Chintan

Re: Issue in expression with set analysis

sunny_talwar — Wed, 18 Jan 2017 13:27:30 GMT

I think you might need to ignore selection in Month field or infact any date and time related fields where you might make selections....

Re: Issue in expression with set analysis

swuehl — Wed, 18 Jan 2017 13:28:07 GMT

As Sunny pointed out, you would need to clear all selections in fields that may interfere with the date range you want to consider.

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

Re: Issue in expression with set analysis

Anonymous — Wed, 18 Jan 2017 13:28:30 GMT

Hello Sunny

I need the month selection. Like if I select Oct -2016, I should get the 6 months data from Aprl to Sep 2016.

The variables work correctly for fetching 6 previous months but the expression does not work.

Thanks & Regards

Chintan

Re: Issue in expression with set analysis

sunny_talwar — Wed, 18 Jan 2017 13:29:54 GMT

That will be handled (or should be handled) by date range itself. Month selection is avoided for direct impact which you are seeing. Give it a try once and see if it isn't working.

If it still doesn't work, I would suggest sharing a sample

Re: Issue in expression with set analysis

Anonymous — Thu, 19 Jan 2017 12:23:08 GMT

Hello Sunny

It does not work after passing month. Is it fine to share a sample with few hundred records?

Thanks & Regards

Chintan

Re: Issue in expression with set analysis

sunny_talwar — Thu, 19 Jan 2017 12:24:02 GMT

That should work

Re: Issue in expression with set analysis

Anonymous — Thu, 19 Jan 2017 12:57:22 GMT

Hello Sunny

PFA Sample for few thousand records debug.

Thanks

Chintan

Re: Issue in expression with set analysis

sunny_talwar — Thu, 19 Jan 2017 13:13:09 GMT

I think TURNOVER value is missing before Apr-2015, but if I select Apr-2015 it gives something like this

=Sum({<MonthYear={"$(='>=' & Date(MonthStart(Max({<TURNOVER = {'*'}>}MonthYear), -6), 'MMM-YYYY') & '<' & Date(Max({<TURNOVER = {'*'}>}MonthYear), 'MMM-YYYY'))"}, Month, Year, Monyr, TDATE, DATE>}TURNOVER)

Re: Issue in expression with set analysis

Anonymous — Fri, 20 Jan 2017 13:04:22 GMT

It works!!

Thanks & Regards

Chintan