topic Set Analysis- As at date when selecting Year and Month in QlikView

Set Analysis- As at date when selecting Year and Month

Wed, 21 Aug 2013 19:08:43 GMT

I hope someone can help me with this one as i am quite new to qlikview and slowly teaching myself how to make it work for me.

Currently I have a scenario where I am just unable to get the required answers. What I want to do is click on Fiscal Year, Then click a specific month. The formula or calculation of the values should be something like this:

Sum (All Transactions with a date = or smaller than the month of the year selected.

I currently have this formula but it only works on Fiscal Year. As soon as I click on an applicable month it will only calculate for that specific month in each of the years smaller or = to the vYTDYear. I should calculated for all months/dates smaller than the Year-Month selection.

Sum({<[Fiscal Year]={"<=$(vYTDYear)"} >} [Amount])

Your help will really be appreciated

I attach my current model

Re: Set Analysis- As at date when selecting Year and Month

arthur_dom — Wed, 21 Aug 2013 21:59:12 GMT

The file have some problem, I can't open it.

But i think i understood the case, on the file I created just now i created a field to the end of Month, so you can just compare it to the variable Vmax that contains the max date.

On the Table i used a expression like yours.

Re: Set Analysis- As at date when selecting Year and Month

Thu, 22 Aug 2013 06:06:51 GMT

Thanks

Can you perhaps show me your formula?

Re: Set Analysis- As at date when selecting Year and Month

arthur_dom — Thu, 22 Aug 2013 12:34:55 GMT

Okay, I used this formula on graph

sum({<MonthEnd={"<=$(vMax)" } >} Expression1)|

the vMax variable is

=date(max(MonthEnd)+1)

and the MonthEnd (on Script) field is

monthend(DateId) as MonthEnd