Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us to spark ideas for how to put the latest capabilities into action. Register here!
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

display of previous data from selected one

i wish to display the data of the previous 12 months.

Upon selecting any month-year(as dimension) i must get data on a line/bar/combo chart of the past 12 months.

I have tried rolling 12 months but its not the sum of past 12 months which i want. i want the chart to display the entire graph data from the selected month to past 12 months.

i am having trouble writing the proper set analysis.

my dimension is [month year]  (jun 2016 type) and measure is [Year Salary]

i am writing my set as such : sum({<  [month year]= "  --This is the part am messing up-- " >}[Year Salary])

please help

1 Solution

Accepted Solutions
susovan
Partner - Specialist
Partner - Specialist

Hi Pooja,

Try this expression,

sum({<@_Date={">=$(=Date(AddMonths( Max(DateField),-12),'DD/MM/YYYY'))<=$(=Date(Max(DateField),'DD/MM/YYYY'))"},MonthField=,QuarterField=,YearField=>}[Year Salary])

Warm Regards,
Susovan

View solution in original post

10 Replies
Gysbert_Wassenaar
Partner - Champion III
Partner - Champion III

See this document: Calculating rolling n-period totals, averages or other aggregations


talk is cheap, supply exceeds demand
Anil_Babu_Samineni

Can you please send me your application

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful
tyagishaila
Specialist
Specialist

Try it

Sum( {<Month = {'>=$(=Addmonths(Datefield,-12))<=$(=Month(Datefield))'} >} [Year Salary])

Not applicable
Author

I tried doing this, however I didn't work the way am looking for.

upon selection, it just shows me that current year. I want it to work in such a way that upon selecting jun 2005, it should display the line graph from jun 2004 till jun 2005.

susovan
Partner - Specialist
Partner - Specialist

Hi Pooja,

Try this expression,

sum({<@_Date={">=$(=Date(AddMonths( Max(DateField),-12),'DD/MM/YYYY'))<=$(=Date(Max(DateField),'DD/MM/YYYY'))"},MonthField=,QuarterField=,YearField=>}[Year Salary])

Warm Regards,
Susovan
Not applicable
Author

I saw the document. However, it is adding the previous months total.

I don't want that. I just want t to display the last n period graph upon selecting any [monthyear].

Upon selecting the year jun 2005, i want the graph to display the previous n period graph pattern.

Not applicable
Author

monthfield, quaterfield are qlik sense functions? and what is @_Date?

Not applicable
Author

kindly please explain your logic of using monthfield, quarterfield, yearfield. It is working perfectly as I wanted.

THANK YOU SO MUCH

susovan
Partner - Specialist
Partner - Specialist

You can use the before expression with out those field that you are mentioned in your last reply.

Here @_Date is a datefield of Database,

Warm Regards,
Susovan