Skip to main content

Qlik

cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable
‎2016-08-01 05:26 AM

Previous 3 month sales

Hi Expert

I have calendar and transaction table as below.

I have filter of Month (Mth) filed in report.

I have to calculate the sum values in SET theory formula like sum({<FiscalYrMth={P3YrMth}>}Sales). eg If i click "Jan' in mth field i would get three possible values in 'P3YrMth' field (201401,201402,201403) so i have to calculate previous three month (incuding current month) values.

  Calendar Table:

FiscalYrMthP3YrMthMthMthCodeYearQuarter
201401201311Nov12014Q1
201401201312Nov12014Q1
201401201401Nov12014Q1
201402201312Dec22014Q1
201402201401Dec22014Q1
201402201402Dec22014Q1
201403201401Jan32014Q1
201403201402Jan32014Q1
201403201403Jan32014Q1
201404201402Feb42014Q2
201404201403Feb42014Q2
201404201404Feb42014Q2
201405201403Mar52014Q2
201405201404Mar52014Q2
201405201405Mar52014Q2
201406201404Apr62014Q2
201406201405Apr62014Q2
201406201406Apr62014Q2
201407201405May72014Q3
201407201406May72014Q3
201407201407May72014Q3
201408201406Jun82014Q3
201408201407Jun82014Q3
201408201408Jun82014Q3
201409201407Jul92014Q3
201409201408Jul92014Q3
201409201409Jul92014Q3
201410201408Aug102014Q4
201410201409Aug102014Q4
201410201410Aug102014Q4
201411201409Sep112014Q4
201411201410Sep112014Q4
201411201411Sep112014Q4
201412201410Oct122014Q4
201412201411Oct122014Q4
201412201412Oct122014Q4

TransTable:

  

FiscalYrMthSales
201401100
201402200
201403300
201404400
201405500
201406600
201407700
201408800
201409900
2014101000
2014111100
2014121200
2015011300
2015021400
2015031500
2015041600
2015051700
2015061800
2015071900
2015082000
2015092100
2015102200
2015112300
2015122400
277 Views
0 Likes
3 Replies
kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP
‎2016-08-01 05:41 AM

Hi,

You should link the table using P3YrMth from table 1 and  FiscalYrMth from table 2.

Regards,

Kaushik Solanki

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
207 Views
beck_bakytbek
Master
Master
‎2016-08-01 05:48 AM

Hi Ramu,

check this Topic, that can contribute to solve your issue:

Qlikview Last 7 Days Sales by RFB 223 - YouTube

i hope that helps

beck

207 Views
0 Likes
sunny_talwar
MVP
MVP
‎2016-08-01 06:42 AM

In response to kaushiknsolanki

On a similar note, look at The As-Of Table‌ to get more idea of how this can be done.

207 Views
0 Likes