Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Show past Sixth month total when current month is selected

Hi,

I have a text box to show SUM(Sales) for selected MonthYear lets say Mar-18.

Now I need another text box that shows SUM(Sales) for Oct-17??

Lets say user selected Jan-18 the second text box has to show total for Jul-17.

I tried MonthYear-6 it is not working

Any help is appreciated,

1 Solution

Accepted Solutions
sasiparupudi1
Master III
Master III

Try

Date(Addmonths(Date#(Max(MonthYear),'MMM-YY'),-6),'MMM-YY') in a text box and see if it return six months back yearmonth value. if it does then use the below set analysis

(sum({<MonthYear={"$(=Date(Addmonths(Date#(Max(MonthYear),'MMM-YY'),-6),'MMM-YY'))"}>}if(not match([Vehicle Make],'*HITACHI','*NEWMAN'),[Earned Sales])))

-

(sum({<MonthYear={"$(=Date(Addmonths(Date#(Max(MonthYear),'MMM-YY'),-6),'MMM-YY'))"}>}[Claims Paid])

+

sum({<MonthYear={"$(=Date(Addmonths(Date#(Max(MonthYear),'MMM-YY'),-6),'MMM-YY'))"}>}if(not match([Vehicle Make],'*HITACHI','*NEWMAN'),[Sales Pending])))

View solution in original post

10 Replies
rubenmarin

Hi rs su, the final expression can be different based on the data model and the available fields,in example, if you have a field like:

Date(MakeDate(Year(date), Month(date)), 'YYYYMM') as YearMonth

The expression can be like:

Sum({<YearMonth={"$(=Date(AddMonths(Max(YearMonth), -6), 'YYYYMM'))"}, Year, Month>} Sales)

Anonymous
Not applicable
Author

Sorry not working.

shiveshsingh
Master
Master

Can you share sample app?

qv_testing
Specialist II
Specialist II

Try This,


Sum({<Date={'>$(=AddMonths(Max(Date),-6))'}>} Sales)

isingh30
Specialist
Specialist

Can you share your data or app?

Anonymous
Not applicable
Author

(sum(if(not match([Vehicle Make],'*HITACHI','*NEWMAN'),[Earned Sales])))-(sum([Claims Paid])+sum(if(not match([Vehicle Make],'*HITACHI','*NEWMAN'),[Sales Pending])))

This is my expression in text box for current year which is showing right lets say Jan-18, I need the same expression for six months old data Jul-17 in another text box

Anonymous
Not applicable
Author

(sum(if(not match([Vehicle Make],'*HITACHI','*NEWMAN'),[Earned Sales])))-(sum([Claims Paid])+sum(if(not match([Vehicle Make],'*HITACHI','*NEWMAN'),[Sales Pending])))

This is my expression in text box for current year which is showing right lets say Jan-18, I need the same expression for six months old data Jul-17 in another text box

Anonymous
Not applicable
Author

(sum(if(not match([Vehicle Make],'*HITACHI','*NEWMAN'),[Earned Sales])))-(sum([Claims Paid])+sum(if(not match([Vehicle Make],'*HITACHI','*NEWMAN'),[Sales Pending])))

This is my expression in text box for current year which is showing right lets say Jan-18, I need the same expression for six months old data Jul-17 in another text box

ajaysingh285
Contributor III
Contributor III

Hi RS,

Its pretty much simple. Just use two expression.

1. =Sum({<CalMonthYear={"$(=Monthname(addmonths(Max(CalDate),-6)))"}>} Sales)

It is for calculating the last 6 month sales

2. =Sum({<CalMonthYear={"$(=Monthname((Max(CalDate))))"}>} Sales)

It is for calculating the current month sale.

Regards,

Ajay