Sum Set of Data Dynamically By Date

[#1]

Hi All,

I'm new to QlikView and need pointed in the right direction.

I have added Straight Table Chart with a list of Invoice numbers and an Expression Column to display the Invoice Total for the current Month.

I have one dimension [Invoice Number]

My Expression column is as follows:

Label - "Current Month Total"

Expression - =Sum ( { $ < [Invoice Date] = { "2010-05*" } > } [Invoice Amt])

The Straight table will then display all invoice numbers and the total for the current month as expected.

This is a problem because I need to amend the Dimension every month to keep it up to date. What I want to do is dynamically create the filter for the current month. I have tried the following but is causes errors in the Expression Editor:

=Sum ( { $ < [Invoice Date] = { mid(today(1), 7,4) & mid(today(1), 4, 2) & "*" } > } [Invoice Amt])

Thanks in Advance,

Sean

[spsrk_84]

Hi,

Instead of passing date Directly break the Invoice date into Invoice year and Invoice Month

write the expression like this dynamically

Sum ( { $ < [Invoice Year] = { Max(Year(InvoiceDate) } ,

{ Invoice Month] = { Max(Month(InvoiceDate)}, > } [Invoice Amt])

see this expression gives result by default fot current year and current month

Check this

Regards,

Ajay

[#3]

Hi Ajay,

Thanks for the reply, but it appears that the Set does not like functions in the filter. The error that appears is:

Bad Field Name(s): Max

Any thoughts?

Sean

[#4 · Accepted solution]

Hi,

I found a solution, don't know if it is the best way though! I created a variable called currentMth

where currentMth = "'&mid(today(1), 7,4) & '-'& mid(today(1),4, 2)&'*"'

Then in my set analysis expression column I did the following:

=Sum ( { $ < [Invoice Date] = {$(currentMth)} > } [Invoice Amt])

It dynamically changes the result based on the current month.