Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

pinkzfairy
New Contributor II

Pivot Table Monthly Expression Date Range

Hi All,

I have pivot table monthly with filter Year = 2016 like in image attach.

I want to get sum sales value with date range each month.

Example :

Jan 2016, i want to get sum sales value from 1-Feb-2015 to 31-Jan-2016

Feb 2016, i want to get sum sales value from 1-Apr-2015 to 29-Feb-2016

I've tried this :

sum({<[Full Date]={">=$(ADDYEARS(ADDMONTHS(MakeDate([Year Num],[Month Num],1),1),-1))<=$(MonthEnd(MakeDate([Year Num],[Month Num],1)))"}>} [Sales Value])

But it didn't get the right value.

Need your help for the expression

Many Thanks

12 Replies

Re: Pivot Table Monthly Expression Date Range

Can you try this:

RangeSum(Above(Sum({1} [Sales Value]), 0, 12)) * Avg(1)

pinkzfairy
New Contributor II

Re: Pivot Table Monthly Expression Date Range

Hi, thanks for your help, but it showing only accumulation in year selection.

I'm attach the qvw file, you can check the right value in tab "Check Value YoY" by selecting the month.

What I want to do is show 12 months sales, For example

Jan 2016, i want to get sum sales value from 1-Feb-2015 to 31-Jan-2016

Feb 2016, i want to get sum sales value from 1-Apr-2015 to 29-Feb-2016

and so on.

Any help you can offer would be appreciated !

christiana
New Contributor III

Re: Pivot Table Monthly Expression Date Range

Hi,

did you find a solution? I have the same problem.

Many thanks to you!

lakkydev
Contributor II

Re: Pivot Table Monthly Expression Date Range

Try this

=Sum({$<Month=, Year=,

[Full Date]={">=$(=MonthStart(AddMonths(Max([Full Date]),-12)))<$(=MonthEnd(AddMonths(Max([Full Date]),0)))"}>} [Sales Value])

christiana
New Contributor III

Re: Pivot Table Monthly Expression Date Range

I don't get this working.

I would like to have something like this:

Colum A is the introduction year of a product, line 1 is the postin period. I would like to see per posting period always the last 12 months of the introduction year.

So for example we are in August 2015, then I would like to see all products which have been introduced between Aug 2014 and Aug 2015.

Is this possible?

Many thanks to you!

lakkydev
Contributor II

Re: Pivot Table Monthly Expression Date Range

can you attach your Qvw , so that we can easy to solve the issue

the above expression will work for last 12 months from selected month

christiana
New Contributor III

Re: Pivot Table Monthly Expression Date Range

I created a Test Application, with Artikel, IntroDate, PostingDate & Sales

lakkydev
Contributor II

Re: Pivot Table Monthly Expression Date Range

Have a look

christiana
New Contributor III

Re: Pivot Table Monthly Expression Date Range

Thanks a lot.

Unfortunately this is not what I'm looking for.

I'm looking for something like this:

Column A: intro date

line 1: posting date

Is something like this possible?