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: 
Announcements
Qlik Connect 2024! Seize endless possibilities! LEARN MORE
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-11-28 05:45 AM

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

3,138 Views
0 Likes
18 Replies
Anonymous
Not applicable
‎2016-11-30 07:08 AM
Author

In response to sunny_talwar

Hi Sunny,

I attached the sample data in excel format. Could you please have a look at this?

Below is the code im trying to use as a 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

Preview file
122 KB
748 Views
0 Likes
pathiqvd
Creator III
Creator III
‎2016-11-30 07:33 AM

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

2,929 Views
Anonymous
Not applicable
‎2016-11-30 07:54 AM
Author

In response to pathiqvd

Thanks a mil Lakshmi and Sunny. It has been sorted.

As per your advise I follow the sets and its resolved now.

Initially I was trying to put it on calculated dimension. and as per your advise I put this login on expression.

Thanks again

Farrukh

748 Views
0 Likes
Anonymous
Not applicable
‎2016-11-30 10:12 AM
Author

In response to pathiqvd

Hi Lakshmi,

The above data I am using with Cyclic (by Region and Company) and I have the sum of sale by [Month Year], Region or Company.

also in another expression I am trying to do the total sum by [Month Year] and dividing to category sale to get a percentage.

Could you please advise me where and what I am missing.

Sum({$<Year=, Month=, [Year Month]={">=$(=Date(MonthStart(AddMonths(Min(Date),-11)),'YYYYMM'))<=$(=Date(MonthStart(AddMonths(Max(Date),0)),'YYYYMM'))"}>} Sales) / sum(Total sum({$<Year=, Month=, [Year Month]={">=$(=Date(MonthStart(AddMonths(Min(Date),-11)),'YYYYMM'))<=$(=Date(MonthStart(AddMonths(Max(Date),0)),'YYYYMM'))"}>} Sales))

Kind regards,

Farrukh

748 Views
0 Likes
pathiqvd
Creator III
Creator III
‎2016-11-30 10:26 AM

In response to Anonymous

Hi,

Remove Total put All or {1}

Check this

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

/

sum({<Year=, Month=, [Year Month]={">=$(=Date(MonthStart(AddMonths(Min(Date),-11)),'YYYYMM'))<=$(=Date(MonthStart(AddMonths(Max(Date),0)),'YYYYMM'))"}>}All Sales)

((((or))))

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

/

sum({1<Year=, Month=, [Year Month]={">=$(=Date(MonthStart(AddMonths(Min(Date),-11)),'YYYYMM'))<=$(=Date(MonthStart(AddMonths(Max(Date),0)),'YYYYMM'))"}>} Sales)

748 Views
0 Likes
Anonymous
Not applicable
‎2016-11-30 11:19 AM
Author

In response to pathiqvd

Hi Lakshmi,

Can you please have a look at attached sample?

I'm trying to get a percentage of each region by diving the total sum of sale by month.

Farrukh

testchart.qvw
748 Views
0 Likes
sunny_talwar
MVP
MVP
‎2016-11-30 11:23 AM

In response to Anonymous

Is this what you are trying to do?

Capture.PNG

=Sum({$<Year =, Month =, YearMonth = {">=$(=Date(MonthStart(AddMonths(Min(SaleDate), -5)), 'YYYYMM'))<=$(=Date(MonthStart(AddMonths(Max(SaleDate), 0)), 'YYYYMM'))"}>} Sale)/Sum(TOTAL <MonthYear> {$<Year=, Month=, YearMonth = {">=$(=Date(MonthStart(AddMonths(Min(SaleDate), -5)), 'YYYYMM'))<=$(=Date(MonthStart(AddMonths(Max(SaleDate), 0)), 'YYYYMM'))"}>} Sale)

testchart.qvw
748 Views
Anonymous
Not applicable
‎2016-11-30 11:34 AM
Author

In response to sunny_talwar

Thanks Sunny,

748 Views
0 Likes
Anonymous
Not applicable
‎2016-12-01 05:18 AM
Author

In response to sunny_talwar

Hi Sunny,

How can I categorize the percentage for cyclic group in different column, as example shown in below table:

I am using the cyclic group and it has calculated dimension for top 3 sales by rank.

Also I tried to use the GetCurrentField(GroupName) but it did not work. I do not want to use hard code for region or company name it could be change by top or bottom sale.

Can you please advise me which statement do I need to use.

Currently I have the below expression to calculate the percentage but its in one column.

=Sum({$<Year =, Month =, YearMonth = {">=$(=Date(MonthStart(AddMonths(Min(SaleDate), -5)), 'YYYYMM'))<=$(=Date(MonthStart(AddMonths(Max(SaleDate), 0)), 'YYYYMM'))"}>} Sale)/Sum(TOTAL <MonthYear> {$<Year=, Month=, YearMonth = {">=$(=Date(MonthStart(AddMonths(Min(SaleDate), -5)), 'YYYYMM'))<=$(=Date(MonthStart(AddMonths(Max(SaleDate), 0)), 'YYYYMM'))"}>} Sale)

   

MonthYearRegion=Sum({$<Year=, Month=, YearMonth={">=201304<=201309"} >} Sale)WestEastSouthNorth
40291.599
Apr-2013West941.2814.94%
Apr-2013East1551.1 24.63%
Apr-2013South1777.457 28.22%
Apr-2013North2028.513 32.21%
May-2013West941.2814.80%
May-2013East1551.1 24.39%
May-2013South1777.457 27.95%
May-2013North2088.842 32.85%
Jun-2013West941.2814.94%
Jun-2013East1551.1 24.63%
Jun-2013South1777.457 28.22%
Jun-2013North2028.513 32.21%
Jul-2013West941.2811.88%
Jul-2013East1551.1 19.57%
Jul-2013South1777.457 22.43%
Jul-2013North3654.965 46.12%
Aug-2013East1551.121.91%
Aug-2013West1722.24 24.33%
Aug-2013South1777.457 25.11%
Aug-2013North2028.513 28.65%
Sep-2013South1030.25516.27%
Sep-2013East1551.1 24.50%
Sep-2013West1722.24 27.20%
Sep-2013North2028.513 32.04%
748 Views
0 Likes