Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Last 6 months data

Dear All

I have a requirement where I need to calculate average of last 6 months sales.

Example:  If we are in December we need to calculate average from June to November..

In Jan,  from July to Dec and so on.    Kindly help to achieve this possibility

Thanks & Regards

Chintan Gala

20 Replies
veeranj
Creator II
Creator II

Hi Chintan,

You can use Rangesum function  like rangesum(sum(sales),0,6) where as Month is your dimension .

Thanks,

Veera

PrashantSangle

Hi,

Use rangeavg()

Regards

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂
Anonymous
Not applicable
Author

Or is it better to show running average by taking last 180 days from today?

Thanks & Regards

Chintan Gala

PrashantSangle

not necessary 6 months = 180 days

Regards,

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂
vinieme12
Champion III
Champion III

where are you trying to show this info Textbox? Straight Table (Rolling Average) ? or script?

create a YearMonth field in your data table to simplify the calculations

example: 201601,201602 and so on

and vStartYearMonth = NUM(year(Today())) & NUM(month(Today()),'00')

and vEndYearMonth = Addmonths( Date($(vStartYearMonth),'YYYYMM'),-6)


Just ensure your Month variables are '00'  format meaning Jan is '01' and not '1'

You expression should then be

SUM ( {< YearMonth = {">=$(vStartYearMonth)<$(vEndYearMonth)"} >} Sales)

with YearMonth you will be compare months over different year example 201611 to 201704

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
Anonymous
Not applicable
Author

Thanks Vineeth. I will try the same and let you know.

I am trying to do this in front end, probably pivot table or straight table.

Thanks & Regards

Chintan Gala

Anonymous
Not applicable
Author

Dear Vineeth

The vEndYearMonth   variable is not giving 6 month previous YearMonth.  It gives a junk date as 26-09-2654 something. Can you please suggest if any modification is required?

Thanks & Regards

Chintan Gala

parimikittu
Creator II
Creator II

Hi Chintan, It is always better to create a numeric value(extra column) for each month for whole master calendar. for example month,year,id This id has a numeric value. Month_numeric: Load Distinct     Month,     Year,     Rowno()+100 as Mon_num resident MasterCalendar Join the above with Master Calendar. SUM ( {< Month = {"=$(=Max(Mon_num)-7)"} >} Sales)

Anonymous
Not applicable
Author

Dear Vj

Thanks for your reply. I will try the same and let u know if I am able to achieve the required result.

Thanks & Regards

Chintan Gala