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

[#2]

Try to convert your date into number using the num function, like this

Sales:

Load

num(SalesDate) as SalesDate_Num,

SalesDate,

SaleAmount

inline

[

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 ,    50   

  1/01/2012 ,    24

  3/04/2012  ,   10

  ];

Copy paste this to a dummy qvw and try. Then you subtract the dates easily.

Then you can write a condition say, if(SalesDate- num(Today())>365, 'Do this', 'Do that');

Hope this helps,

Bikash Debnath

[userid128223]

That suggetion will not work. Its huge table trying to convert that into Inline will be nighmare.

The date function is not an issue. It is how to read each line of transaction data, and aggr (sum the SaleAmount where)  only if the condition of 12 months or less is true.

[userid128223]

1. Each line needs to be read
2. if date from today is greater then 12 months, aggregate Sales Amount only for those line.

[#5]

ooooppss my bad....please excuse me, I wasn't clear enough while explaining.

I didn't mean to do an inline load on your data. I used your sample data set and loaded into my qvw using an inline.

I think you shall have to do it in two steps,

first create a flag( a new field) if(num(SalesDate)-num(Today())<365,'Y', 'N') as Flag.

Load this table using the Resident load and

sum the total if the flag is 'Y' and zero for 'N'.

Drop the first table later.

Thanks,

Bikash

[chematos]

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

Check the quotes, may be I missed any.

hope this helps

[MayilVahanan · Accepted solution]

Hi,

     Try this,

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

Hope it helps

[userid128223]

I don't think this aggregates each row of SaleAmount based on date. I think  your statement just checks max date and if it is less then 12 months it does total sum. Which is not what I require.

The answer in my case should be

10/02/2010     55      FALSE

11/03/2009     44      FALSE

  5/05/2011     33      FALSE

  2/04/2011     11      FALSE

  1/07/2011     3        TRUE

  5/11/2012     6          TRUE

  2/02/2012     18         TRUE

  1/01/2012     24     TRUE    

  3/04/2012     10     TRUE

So I want the answer for total of all All True.

[#9]

yeah then you should write

if(flag='TRUE', sum(SaleAmount),0) in your expression.

This should work.

[MayilVahanan]

Hi,

     Check the below file. Hope it helps