Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Issue in expression with set analysis

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

1 Solution

Accepted Solutions
sunny_talwar

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

Capture.PNG

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

View solution in original post

12 Replies
swuehl
MVP
MVP

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

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


Dates in Set Analysis

sunny_talwar

May be ignore selection in Month field

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

Anonymous
Not applicable
Author

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

Thanks & Regards

Chintan

sunny_talwar

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

swuehl
MVP
MVP

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

Anonymous
Not applicable
Author

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

sunny_talwar

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

Anonymous
Not applicable
Author

Hello Sunny

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

Thanks & Regards

Chintan

sunny_talwar

That should work