Solved: Re: Show all record of last 12 months - Qlik Community

Anonymous · ‎2016-11-28

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

pathiqvd · ‎2016-11-30

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

MK_QSL · ‎2016-11-28

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.

Anonymous · ‎2016-11-28

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

MK_QSL · ‎2016-11-28

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..

Anonymous · ‎2016-11-28

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 · ‎2016-11-28

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

Anonymous · ‎2016-11-28

Hi Manish,

Could you please see the attached testchart?

Kind regards,

Farrukh

sunny_talwar · ‎2016-11-28

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

Anonymous · ‎2016-11-30

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 · ‎2016-11-30

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