topic Re: Can anyone help me with Set Analysis Problem on calculating the monthly total? in QlikView

Can anyone help me with Set Analysis Problem on calculating the monthly total?

Wed, 17 Feb 2016 14:51:26 GMT

Hi Experts,

Thanks for taking time to look at my question.

I want to calculate the monthly total quotes(for insurance) based on different areas.

First. I was trying to calculate the MTD (Month to Date) total quotes by using the expression as follow, I think it works.

=sum({<Month = {$(=Month(Today()))}, Year = {$(=Year(Today()))},QuoteDate=>}QuoteCount)

Then, I want to calculate the total quotes for previous two months separately(dynamic month): Dec( from Dec.1.2015--Dec.31.2015) and last year Novenmer,but I don't know how to do it. The data structure is as follow picture:

Can anybody help me with it? Thanks for all your help. Appreciate it!

Re: Can anyone help me with Set Analysis Problem on calculating the monthly total?

Chanty4u — Thu, 18 Feb 2016 09:37:06 GMT

chk dis

Set Analysis for certain Point in Time

Re: Can anyone help me with Set Analysis Problem on calculating the monthly total?

jonathandienst — Thu, 18 Feb 2016 09:45:55 GMT

Please post a sample qvw file with some representative data.

Re: Can anyone help me with Set Analysis Problem on calculating the monthly total?

PrashantSangle — Thu, 18 Feb 2016 09:50:45 GMT

Hi,

are you calculating Month and Year field or it is already available in Database???

Considering you are calculating Month and Year field from QuoteDate

then

Expression for MTD

=sum({<QuoteDate={">=$(=date(monthStart(max(QuoteDate))))<=$(=date(max(QuoteDate)))"}>}QuoteCount)

MTD-1

=sum({<QuoteDate={">=$(=date(monthStart(addMonths(max(QuoteDate),-1))))<=$(=date(addMonths(max(QuoteDate),-1)))"}>}QuoteCount)

MTD-2

=sum({<QuoteDate={">=$(=date(monthStart(addMonths(max(QuoteDate),-2))))<=$(=date(addMonths(max(QuoteDate),-2)))"}>}QuoteCount)

Use AddMonths()

Regards