topic Re: Going back in time - need help in Set Analysis, expression building in QlikView

Going back in time - need help in Set Analysis, expression building

gerrycastellino — Wed, 11 Oct 2017 19:55:03 GMT

I have a situation where I need to build an expression based on previous months or previous years data based on what the user has selected. For this example I need to go back 1 month. The date is not a dimension in the straight table.

sum( {{< %DIM_DATE_KEY= P({1<%DIM_DATE_KEY={"$(=Date(AddMonths(max(%DIM_DATE_KEY),-1)),'MM/DD/YYYY')"} >} %DIM_DATE_KEY) >} CALL_COUNT)

Right now I have this expression showing as follows in the straight table - notice the data for the previous period is blank when I select september 01, 2017:

I have data for August as seen in the attachment.

Re: Going back in time - need help in Set Analysis, expression building

swuehl — Wed, 11 Oct 2017 20:02:59 GMT

Looks like the dollar sign expansion shows a syntax error:

Try

{'$(=Date(AddMonths(max(%DIM_DATE_KEY),-1),'MM/DD/YYYY'))'}

Re: Going back in time - need help in Set Analysis, expression building

gerrycastellino — Wed, 11 Oct 2017 20:50:43 GMT

Stefan:

maybe to simplify it, if I can show in a list box , the prior months periods of what's been selected, that's what I'm looking for.

As you can see here, it's taking the max

See attachments and image.

Gerry.

Re: Going back in time - need help in Set Analysis, expression building

swuehl — Wed, 11 Oct 2017 20:57:40 GMT

If you select a continuous range, you can use a numeric search:

=aggr(only({<DatePeriod=P({1< DatePeriod={">=$(=Date(AddMonths(Min(DatePeriod),-1),'MM/DD/YYYY'))<=$(=Date(AddMonths(Max(DatePeriod),-1),'MM/DD/YYYY'))"} >} ) >} DatePeriod), DatePeriod)

Re: Going back in time - need help in Set Analysis, expression building

swuehl — Wed, 11 Oct 2017 21:00:06 GMT

I am not 100% sure what you want to achieve, but I think you don't need the p() function here:

=aggr(only({<DatePeriod={">=$(=Date(AddMonths(Min(DatePeriod),-1),'MM/DD/YYYY'))<=$(=Date(AddMonths(Max(DatePeriod),-1),'MM/DD/YYYY'))"} >} DatePeriod), DatePeriod)

Re: Going back in time - need help in Set Analysis, expression building

gerrycastellino — Wed, 11 Oct 2017 21:22:40 GMT

I'm trying to apply this to a sum where I'm looking at the previous period from what's being selected:

I put your expression in my sum, trying to get this to work, but it's still showing '0' 's in my straight table.

The date range is now looking the way I want it.

sum( {{< %DIM_DATE_KEY= P({1< %DIM_DATE_KEY={">=$(=Date(AddMonths(Min(%DIM_DATE_KEY),-1),'MM/DD/YYYY'))<=$(=Date(AddMonths(Max(%DIM_DATE_KEY),-1),'MM/DD/YYYY'))"} >} %DIM_DATE_KEY) >} CALL_COUNT)

Re: Going back in time - need help in Set Analysis, expression building

swuehl — Wed, 11 Oct 2017 21:29:13 GMT

Guess you don't make your selections in the date key?

Then you may need to clear all user selections that may interfere with your set modifier in the set analysis (or use set identifier 1 in your main set expression).

Have a look at

https://aftersync.com/blog/the-magic-of-set-analysis-point-in-time-reporting

Re: Going back in time - need help in Set Analysis, expression building

gerrycastellino — Wed, 11 Oct 2017 21:30:50 GMT

Stefan:

I will be making date selections in the datekey.

Gerry.

Re: Going back in time - need help in Set Analysis, expression building

swuehl — Wed, 11 Oct 2017 21:34:31 GMT

Then please update your sample application with the chart & expression in question.