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: 
Not applicable

How to show the Sales for alternate six month in setanalysis

Hi,

  I want to show the sales of alternate 6 months from backward of maximum orderdate in the databese.i use the following expression it works correctly. Can any one help  how to generalize this function as a single line expression

=SUM({<[Order Date]={">=$(=MonthStart(($(#vMaxOrderDate))))<=$(=MonthEnd(($(#vMaxOrderDate))))"},Year=,Month=,Quarter=>

+

<[Order Date]={">=$(=MonthStart(($(#vMaxOrderDate)),-1))<=$(=MonthEnd(($(#vMaxOrderDate)),-1))"},Year=,Month=,Quarter=>

+

<[Order Date]={">=$(=MonthStart(($(#vMaxOrderDate)),-3))<=$(=MonthEnd(($(#vMaxOrderDate)),-3))"},Year=,Month=,Quarter=>

+

<[Order Date]={">=$(=MonthStart(($(#vMaxOrderDate)),-5))<=$(=MonthEnd(($(#vMaxOrderDate)),-5))"},Year=,Month=,Quarter=>

+

<[Order Date]={">=$(=MonthStart(($(#vMaxOrderDate)),-7))<=$(=MonthEnd(($(#vMaxOrderDate)),-7))"},Year=,Month=,Quarter=>

+

<[Order Date]={">=$(=MonthStart(($(#vMaxOrderDate)),-9))<=$(=MonthEnd(($(#vMaxOrderDate)),-9))"},Year=,Month=,Quarter=>

}NetPrice)

vMaxOrderDate contains Maximum orderdate of my databese.

NetPrice is the one where unitprice*quantity-discount

5 Replies
Not applicable
Author

Hi Amutha,

Create a column with value 1 or Y for the months that you want to pick, and use the variable in the set expression to calculate the sum of those months.

so suppose, for Jan, Mar, May, July, Sept, Nov, 

SUM({<Year=,Month=,Quarter=, Varible={'1'}>}NetPrice)

Thats the easiest method. You might have to include more selections expressions in set analysisRegards,

Abhinava

Not applicable
Author

Hi Abhinav,

 

     If no selection is made then my chart must show the alternate six month from maximum date in the database. But if any year or month will selected means it must show the alternate six month from selected year or month or quarter. So its not possible to set the value 1 for some of the months. Is there any other possibilities For generalizing

Not applicable
Author

Hi,

Anyone have idea to generalize my expression.............

Not applicable
Author

Hi QV Experts,

Any one know how to generalize the expression?........

Not applicable
Author

vDateToday = num(Today())

vSixMonthAgo = num(AddMonths(vDateToday,-6));

vPastSixMonth = '=>$(vSixMonthAgo) <= $(vToday)'


Sum({$<Year=,Date={'$(vPastSixMonth)'}>} NetPrice)