Previous Day Set Analysis

[#1]

Hi,

I've got so much support from this forum over my short time using Qlikview, but this one seems to be a new one, so I'm hoping you can help....

I want to allow a user to select any date range and for the expression on the chart to evaluate to the total value for the day BEFORE the period selected. Eg. If I selected Apr-July 2010, I want the value in this expression for 30th May 2010.

I've been trying set analysis, but can't quite get it working.

This works, but returns the value for the first day in the period:

=sum({<Date={"$(=min(Date))"}>}[Amount])

So I tried this and it returns 0:

=sum({<Date={"$(=min(Date)-1)"}>}[Amount])

Strange since a text box with this in it gives me the exact date I'm interested in:

=date(min(Date)-1)

Any help on this is very much appreciated!

Nick

[#2]

You almost had it right there. Your text box uses the Date function to convert Min(Date) - 1 to a date. You didn't do that in your Set Analysis, so your dollar sign expansion is returning a number, not a date. Try:

sum({<Date={"$(=Date(min(Date)-1))"}>}[Amount])

[#3]

Thanks for the reply - I think I already tried that!

I just copied your code straight in and tried it again - it still returns 0. Plus, in a text object if I do:

=only({<Date={"$(=Date(min(Date)-1))"}>}[Date])

It returns nothing. If I do the same without the '-1' it displays the first date in the selection.

[#4]

Odd. QlikView allows you to subtract dates this way. Try putting that expression into a table, but don't give the expression a label. When the chart is rendered, you will see the expression with the dollar sign expansion evaluated. Do two expressions, one with the -1, one without. Then compare what is shown between the inner curly brackets. It's probably a formatting issue.

[#5 · Accepted solution]

Thanks - that's a useful tip. It's also helped me figure out what was hapenning.

The issue wasn't with the expression at all. It was the way I was selecting the date periods. I was choosing Years and Months - but then doing set analysis on Date only. If I change the expression to this:

=only({<Date={"$(=Date(min(Date)-1))"},Year={"*"},Month={"*"}>}[Date])

It returns the correct date. Same for the Sum expression.

Thanks.

[#6]

Nick,

How did you handle the business day issue when you need the data on Monday for Friday?

Kal