Filtering 2 dates with calendars and showing last year

[#1]

Hi,

I am trying to use this solution as calendar filter Date Selections - By Period & Range and it works well, but at the same time I am displaying a table with year volumes and last year volumes and it does not appear anything for last year, while if I don't use the calendars for filtering and I just use the listboxes instead for year, month and day then it works.

My expression for last year is: sum( {$< Year={$(=Only(Year)-1)} >}  Sales )

Calendars are filtering like: >=2014-01-01<=2014-02-28

Am I missing something or it's just that calendar filters do in a way that it prevents this?

Thanks in advance

[Gysbert_Wassenaar]

Can you post a qlikview document that demonstrates the problem?

[#3]

Here is a test document.

If you filter using the two calendar objects then you only see data for actual year.

If you clear selections and filter by the year listbox then you get all.

Thank you very much

[Gysbert_Wassenaar]

I'm afraid I don't see the document. Can you attached it again?

[#5]

Now it should work.

Thanks

[#6]

Hi,

Any solution on that?

Many thanks

[stigchel]

With set analysis you extend/reduce the selected data, in your case you select dates in dated and in your set expression you use Year=. This says that it should ignore any selections in Year, but not in dated, and these are excluding each other. (Year=2013, but selection in dated=>=2014-01-01<=2014-02-28)

Should you simply want the full previous year use:

sum( {$<Dated, Year={$(=Only(Year)-1)} >}  Sales )

So ignore selections in Dated, should you want to use the same selected dates but then a year earlier use:

Sum({<Dated={">=$(=AddMonths(Date(vDatedStart),-12))<=$(=AddMonths(Date(vDatedEnd),-12))"}>} Value)

See attached

[#8]

Hi,

What I try to do is to use the year and month selectors OR the start-end date calendars. Showing one or another with a button and then controlled using a variable.

So I guess I could do something like this in order to show values for the last period data beeing natural year or the period the user selects:

if(    $(variable)=1,

    sum( {$< Year= {$(=Only(Year)-1)} >} Value)

,

    sum( {$< Dated={">=$(=AddMonths(Date(vDatedStart),-12))<=$(=AddMonths(Date(vDatedEnd),-12))"} >} Value)

    )

[stigchel · Accepted solution]

Something like that, although I think you don't need the $(variable) (do you want a variable variable??) but something like (and remove the quotes in the contents of vInvDate)

if(vInvDate='Date',

Sum({<Dated={">=$(=AddMonths(Date(vDatedStart),-12))<=$(=AddMonths(Date(vDatedEnd),-12))"}>} Value),

Sum({<Dated={">=$(=Date(vDatedStart))<=$(=Date(vDatedEnd))"}>} Value)

)

If this has answered your question, could you please mark the relevant answers correct and/or helpfull? It will help others find correct answers and contributing members know this requires no more attention.

[#10]

Hi Piet,

That's correct I can use the vInvDate variable.

But now I'm facing another problem, that example document works perfectly but when I do it in the original document it does not work when I apply the filters from the calendar objects.

Something about the date field. In the example that field displays dates with the format DD-MM-YYYY and in my own document it displays with YYYY-MM-DD so I try to do something like this:

='>=$(=date($(vDateStart),"YYYY-MM-DD"))'

But no luck, Any idea? I see this field is recognized by QV as date as it is showing $date in the table viewer. So?

Many thanks