Aggr with If condition.

[userid128223]

Let say I have a table like this

SalesDate     SaleAmount

10/02/2010     55

11/03/2009     44

  5/05/2011     33    

  2/04/2011     11

  1/07/2011     3

  5/11/2012     6

  2/02/2012     18    

  1/01/2012     24

  3/04/2012     10

1) How can I show total SalesAmount if SalesDate is less then 12 months old from todays date. In this case expression will read each record and add total sales if the date is not older then 12 months.

I want to show this is a text box.

The below expression does not work because it cannot read each salesdate individually and total it.

if(SalesDate > AddMonths(today(),-12),sum(SalesAmount),0)

please help

thanks

[userid128223]

Amazing thanks.

If i were to color the statement red if the number is negative in above subtraction statement.

2nd.

The whole set analysis works on basis of todays() date selection. What if I want to make that into based on selection of certain date from date picker column. Can i pass that as a variable to set analysis statement.

[MayilVahanan]

Hi,

     Ya , you can able to give red color if the number is negative in above subtraction statement, by using

          if(exp2 - exp1 <0 , red(),green()) in expression tab click '+' sign and give in background color in chart

for 2nd:

     ya you can pass as a variable to set analysis statement

     =sum({<SalesDate={'>=$(=AddMonths($(=variablename),-12))'}>}SaleAmount)

     Can you please able to mark as helpful answer or correct answer, if you get the soution for your requirement. So that others as well as i can know which posts are helpful to you.

Hope it helps

[userid128223]

This entire post has lot of great answer. I will go thru each and mark them as helpful and correct. Thanks for you help.

I am facing one issue.

When i select any day, month, year from list boxes the answer from below statement changes to show that time period data. how can exclude the months, day, and year selection not to have impact on my answer.

=sum({<SalesDate={'>=$(=AddMonths(Max(SalesDate),-12))'}>}SaleAmount)

[MayilVahanan]

Hi,

     you can exclude them by using,

     =sum({<SalesDate={'>=$(=AddMonths(Max(SalesDate),-12))'} , Year =, Month =, Day=>}SaleAmount)

In this, Year = means exclude it from this calculation..

Hope it helps

[userid128223]

It didn't work. When i select month, year, or day. the calculation changes.

[chematos]

You could try with your Date of your calendar instead SalesDate

=sum({<SalesDate={'>=$(=AddMonths(CalendarDate),-12))'} , Year =, Month =, Day=>}SaleAmount)

[userid128223]

Interesting discovery. Please explain why?

Scenerio 1

When i use master calander function like below to create my months, year, day etc. The above exclude function will not work.

http://community.qlik.com/thread/48693

However

Scenerio 2

when create month, year, day out of sales date without using master calander (which actually takes min, max and calculates each day of entire period instead of sales trasaction day).

ie

Year(Salesdate) As Year,

why would that happen.

[chematos]

I think that the best way is to make a MasterCalendar and link by SalesDate your inline table or whatever.

If you select a Date from your calendar and you have the same SalesDate, you will see your sales of that day, but you could use that calendar to chose the intervals you want just like the expression I wrote before.

The filters works depending of your connections.

Hope you understand it

[userid128223]

my calander is based on sales date.

The below statement is suppose to give me total result, regardless of what is selected in month, year or day.

     =sum({<SalesDate={'>=$(=AddMonths(Max(SalesDate),-12))'} , Year =, Month =, Day=>}SaleAmount)

however that is not happening. When I click on either month, year or day the answer changes.

[chematos]

Try with TOTAL

=sum({<SalesDate={'>=$(=AddMonths(Max(SalesDate),-12))'} , Year =, Month =, Day=>}TOTAL SaleAmount)