topic Re: Pivot Table Monthly Expression Date Range in QlikView

Pivot Table Monthly Expression Date Range

pinkzfairy — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Pivot Table Monthly Expression Date Range

sunny_talwar — Fri, 05 Aug 2016 10:08:49 GMT

Can you try this:

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

Re: Pivot Table Monthly Expression Date Range

pinkzfairy — Tue, 09 Aug 2016 03:35:44 GMT

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 !

Re: Pivot Table Monthly Expression Date Range

christiana — Fri, 10 Mar 2017 13:01:07 GMT

Hi,

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

Many thanks to you!

Re: Pivot Table Monthly Expression Date Range

Anonymous — Fri, 10 Mar 2017 13:09:14 GMT

Try this

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

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

Re: Pivot Table Monthly Expression Date Range

christiana — Fri, 10 Mar 2017 13:26:38 GMT

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!

Re: Pivot Table Monthly Expression Date Range

Anonymous — Fri, 10 Mar 2017 13:34:23 GMT

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

Re: Pivot Table Monthly Expression Date Range

christiana — Fri, 10 Mar 2017 14:01:51 GMT

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

Re: Pivot Table Monthly Expression Date Range

Anonymous — Fri, 10 Mar 2017 14:10:30 GMT

Have a look

Re: Pivot Table Monthly Expression Date Range

christiana — Fri, 10 Mar 2017 14:17:27 GMT

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?

Re: Pivot Table Monthly Expression Date Range

sunny_talwar — Fri, 10 Mar 2017 14:17:53 GMT

May be this

=If(Sum(TOTAL <[Posting Date]> Aggr(If(Sum(Sales) > 0, 1, 0), [Introduction Date], [Posting Date])) - RangeSum(Above(Sum(Aggr(If(Sum(Sales) > 0, 1, 0), [Introduction Date], [Posting Date])), 0, RowNo())) < 12, Sum(Sales))

Re: Pivot Table Monthly Expression Date Range

christiana — Fri, 10 Mar 2017 16:05:35 GMT

This is very strange.

In the test application it is working, in my "big" application not...

I need to dig into it why.

Another question - if I select an article it seems not to work anymore:

One article should be only visible during 12 months, but as soon as I select one article it is visible during the whole time when it has Sales...

Any idea how to restrict it to 12 months?

Re: Pivot Table Monthly Expression Date Range

sunny_talwar — Fri, 10 Mar 2017 16:31:21 GMT

Change the expression to this

=If(Sum(TOTAL <[Posting Date]> {<Artikel>} Aggr(If(Sum({<Artikel>}Sales) > 0, 1, 0), [Introduction Date], [Posting Date])) - RangeSum(Above(Sum({<Artikel>}Aggr(If(Sum({<Artikel>}Sales) > 0, 1, 0), [Introduction Date], [Posting Date])), 0, RowNo())) < 12, Sum(Sales))