Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join us at the Cloud Data and Analytics Tour! REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
farrukh_shaikh
Creator II
Creator II

Show all record of last 12 months

Hi Sunny,

Could you please advise me how to restrict the dimension?

My current selection is year 2016  and month is Aug.

below is the table format.

[Month Year] , Region, Company , Sales

(Want to display all the record from current date till last 12 month)

Could you please advise me how to show all the records by range dimension.

the below condition I put on calculated dimension.

=Aggr(if(([Year Month]<=Date(MonthStart(AddMonths(Min(Date),-11)),'YYYYMM') And [Year Month]<=Date(MonthStart(AddMonths(Min(Date),-11)),'YYYYMM')) , [Month Year]), [Month Year], Company)

Kind regards,

Farrukh

1 Solution

Accepted Solutions
pathiqvd
Creator III
Creator III

Hi,

Please first convert month Year filed into mm-yyyy format use this

Test:

Load *,date(date#(Month_Year,'MMM-YYYY'),'MM/YYYY') as MnthYr_Num,

makedate(year(date#(Month_Year,'MMM-YYYY')),month(date#(Month_Year,'MMM-YYYY')),01) as Date_Test inline

[

Month_Year, Region, Company, Sale

Aug-2015, South, ABC, 473.372

Aug-2015, South, ABC, 9.086

Aug-2015, North, EFG, 60.329

Aug-2015, North, EFG, 1626.452

Aug-2015, South, XYZ, 199.542

Aug-2015, South, XYZ, 10.325

Aug-2015, East, FFF, 103.26

Aug-2015, East, PPP, 26.16

Aug-2015, West, ONO, 4.32

Aug-2015, West, PUY, 51.12

Sep-2015, South, ABC, 7.443

Sep-2015, North, EFG, 336.104

Sep-2015, East, FFF, 99.35

Sep-2015, East, PPP, 85.92

Sep-2015, West, ONO, 11.52

Oct-2015, North, EFG, 5.628

Oct-2015, South, XYZ, 59.945

Oct-2015, East, FFF, 64.32

Oct-2015, East, PPP, 169.68

Oct-2015, West, ONO, 30.96

Oct-2015, West, PUY, 51.12

Nov-2015, South, XYZ, 71

Nov-2015, East, FFF, 90.9

Nov-2015, East, PPP, 29.52

Nov-2015, West, PUY, 11.28

Dec-2015, East, FFF, 131.75

Dec-2015, East, PPP, 750.24

Dec-2015, West, PUY, 780.96

];

after use this exp and select year month field

Sum({<MnthYr_Num={">$(=date(num(date(max(MnthYr_Num),'MM-YYYY'))-90,'MM/YYYY'))<=$(=date(num(date(max(MnthYr_Num),'MM-YYYY')),'MM/YYYY'))"}>}

Sale)

this is for 3 month if you want 12 months 12*30=360 like you write how many months do you want

replace 360 in place of 90

View solution in original post

18 Replies
MK_QSL
MVP
MVP

Dimension

Company

[Year Month]

Expression

SUM({<Year, Month, [Year Month]= {">=$(=Date(MonthStart(Date(Max([Year Month]),'YYYYMM'),-11),'YYYYMM'))<=$(=Date(Max([Year Month]),'YYYYMM'))"}>}Sales)

You can add more dimension if you need.

farrukh_shaikh
Creator II
Creator II
Author

Hi Manish,

I have done this way, but the format I want to display its by dimension.

Could you please advise me about this?

   

Month YearRegionCompanySale
Aug-2015SouthABC473.372
Aug-2015SouthABC9.086
Aug-2015NorthEFG60.329
Aug-2015NorthEFG1626.452
Aug-2015SouthXYZ199.542
Aug-2015SouthXYZ10.325
Aug-2015EastFFF103.26
Aug-2015EastPPP26.16
Aug-2015WestONO4.32
Aug-2015WestPUY51.12
Sep-2015SouthABC7.443
Sep-2015NorthEFG336.104
Sep-2015EastFFF99.35
Sep-2015EastPPP85.92
Sep-2015WestONO11.52
Oct-2015NorthEFG5.628
Oct-2015SouthXYZ59.945
Oct-2015EastFFF64.32
Oct-2015EastPPP169.68
Oct-2015WestONO30.96
Oct-2015WestPUY51.12
Nov-2015SouthXYZ71
Nov-2015EastFFF90.9
Nov-2015EastPPP29.52
Nov-2015WestPUY11.28
Dec-2015EastFFF131.75
Dec-2015EastPPP750.24
Dec-2015WestPUY780.96
MK_QSL
MVP
MVP

Use Pivot Table

1st Dimension Region

2nd Dimension Company

3rd Dimension Month Year

Expression

.....

Now use Month Year as Column... Drag and drop at top of Pivot to make it as column..

farrukh_shaikh
Creator II
Creator II
Author

Hi Manish,

Is there any possibility that I can use [Month Year] in dimension as a straight table?

As I mentioned above that current selected Year is 2016 and Month is Aug, So I need to show all the record in a row. as provided in above example.

Also want to restrict dimension within date range of current selected [Month Year] till last 12 months.

Farrukh

MK_QSL
MVP
MVP

Provide dummy data or sample app to work.. otherwise this would be never ending conversation.

farrukh_shaikh
Creator II
Creator II
Author

Hi Manish,

Could you please see the attached testchart?

Kind regards,

Farrukh

sunny_talwar

Show 5 years worth of data? But 2010 to 2014 when 2013 is selected? What's the logic here?

farrukh_shaikh
Creator II
Creator II
Author

Hi Sunny,

Sorry for the delay,

Basically I am trying to show only the data between two different dates. it could be 6 month or last 1 year, based on selected Month and Year.

Also want to use [Month Year] as a dimension, So If I convert the straight table into chart then I can use [Month Year] in Y axis.

The sample data has last 7 or 8 years sale record.

The format I'm trying to develop as below:

Dimensions:

[Month Year]

Region

Company

Expression:

Sale

Could you please advise me how to show only certain record within two dates (if month and year is selected) with calculated dimension.

Kind regards,

Farrukh

sunny_talwar

Again, I am not sure how you are selecting two dates here? I see only a single Year and Month selected, how will you determine if you want to see 6 months or 1 year or 5 years? I feel like I am not 100% sure I understand what the required output needs to be. If you can put together an Excel file where you can show us what you intend to see based on few selections, the task might become much easier to accomplish.

Best,

Sunny