Skip to main content
Woohoo! Qlik Community has won “Best in Class Community” in the 2024 Khoros Kudos awards!
Announcements
Save $600 on Qlik Connect registration! Sign up by Dec. 6 to get an extra $100 off with code CYBERSAVE: REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
pinkzfairy
Contributor II
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
sunny_talwar

Can you try this:

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

pinkzfairy
Contributor II
Contributor II
Author

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
Contributor III
Contributor III

Hi,

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

Many thanks to you!

Anonymous
Not applicable

Try this

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

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

christiana
Contributor III
Contributor III

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!

Anonymous
Not applicable

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
Contributor III
Contributor III

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

Anonymous
Not applicable

Have a look

christiana
Contributor III
Contributor III

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?