topic Re: Need help in QlikView

Aggr with If condition.

userid128223 — Fri, 29 Jun 2012 19:16:33 GMT

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

Re: Need help

Fri, 29 Jun 2012 19:35:36 GMT

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

Re: Need help

userid128223 — Fri, 29 Jun 2012 19:45:37 GMT

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.

Re: Need help

userid128223 — Fri, 29 Jun 2012 19:51:47 GMT

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

Re: Need help

Fri, 29 Jun 2012 19:57:29 GMT

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

Re: Aggr with If condition.

chematos — Fri, 29 Jun 2012 20:57:33 GMT

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

Check the quotes, may be I missed any.

hope this helps

Re: Need help

MayilVahanan — Fri, 29 Jun 2012 21:12:11 GMT

Hi,

Try this,

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

Hope it helps

Re: Need help

userid128223 — Fri, 29 Jun 2012 21:52:18 GMT

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.

Re: Need help

Fri, 29 Jun 2012 21:54:48 GMT

yeah then you should write

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

This should work.

Re: Need help

MayilVahanan — Fri, 29 Jun 2012 22:08:42 GMT

Hi,

Check the below file. Hope it helps

Re: Need help

Fri, 29 Jun 2012 22:20:50 GMT

Hope this helps.

Re: Need help

userid128223 — Sat, 30 Jun 2012 01:12:04 GMT

Can someone please explain this set analysis step by step.

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

thanks

Re: Need help

tresB — Sat, 30 Jun 2012 04:17:29 GMT

Let me try explain your set analysis:

execution starts from the innermost expression - 'Max(SalesDate)' - it picks the maximum date from SalesDate.
Now 'AddMonths(.....maximum date got from earlier expression),-12' - takes it to 12 months back, i.e. the previous year similar date(if the max date was 1-2-12, now it generates 1-2-11).
now the expression checks for '<SalesDate={'>=.....>' part - which gets the dates from SalesDate which are less than or equal to date generated at the second step (the previous year maximim date).
now 'Sum(....SalesAmonut)' part of expression finally gives you the summation of the SalesAmount for those transaction dates which are generated in third step (dates before or equal to last year maximum date).

Hope this helps.

Regards,

tresesco

Re: Need help

chematos — Mon, 02 Jul 2012 12:42:16 GMT

I haven´t tried this, so could be wrong but I would try it.

Hope this helps.

if(aggr(sum({<SalesDate={'>=$(=AddMonths(Today(),-12)'}>}SalesAmount), SalesDate) > X, 'do this', 'do else')

I´m assuming that you are using SalesDate as dimension in your chart.

May be you coul try to do this:

Calculate dimension:

If(SalesDate>= addmonths(today(),-12), SalesDate) //this returns only the dates since the prior year from today.

Expression:

aggr(sum(SalesAmount),SalesDate)

Good luck, let me know your results.

Regards,

Chema

Re: Need help

userid128223 — Tue, 03 Jul 2012 13:29:57 GMT

Thanks for all the answers, I will try it out today and let you guys know.

I have been able to do simple set analysis and understand them. However this is bit complicated. What is the best place to learn set analysis, syntax. Do you need programming background in order to grasp the concept?

Re: Need help

MayilVahanan — Tue, 03 Jul 2012 13:45:36 GMT

Hi,

Try this below link, hope it will help you to know basic set analysis concept.

http://tools.qlikblog.at/SetAnalysisWizard/QlikView-SetAnalysis_Wizard_and_Generator.aspx?sa=_CNGH

Hope it helps

Re: Need help

userid128223 — Tue, 03 Jul 2012 15:00:12 GMT

Mayil Thanks for the wizard.

Now please tell me how you will come up with below solution using this wizard step by step. Is it possible.

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

Re: Need help

MayilVahanan — Tue, 03 Jul 2012 15:15:23 GMT

Hi,

1.Sum(SaleAmount) gives sum of all the sales amount

2. Max(SalesDate) give the maximum date which you select in your field for example , if you select 5/5/2011,6/5/2011,7/7/2011 in this selection, it gives 7/7/2011

3. AddMonths(startDate,n) function

Returns the date occurring n months after startdate or, if n is negative, the date occurring n months before startdate.

Examples:

addmonths ('2003-01-29',3) returns '2003-04-29'

4. So =sum({<SalesDate={'>=$(=AddMonths(Max(SalesDate),-12))'}>}SaleAmount) in this , for example

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

AddMonths(Max(7/7/2011), -12) gives the date 7/7/2010 ,i.e. previous year of same date

>= means, greater than or equal to 7/7/2010

so from 7/7/2010 to 7/7/2011 sales amount calculate and give result.

Hope it helps

Re: Need help

userid128223 — Tue, 03 Jul 2012 15:40:58 GMT

Thanks Mayil for the answer.

I got the explaination part. Now for the syntax any clues, any tips how to organize and remember.

If i had 2 statement like this on two seperate text box.

Sales Amount store 1

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

Sales Amount Store 2

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

How can subtract 1 from the 2 in seperate text box.

Re: Need help

MayilVahanan — Tue, 03 Jul 2012 15:49:16 GMT

HI,

By practice you can remember easily..See the link which i mentioned in earlier post.That also help you for know more about set anlysis..

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

Itself you give in text box ..But the result is szero only i think. because both are same expression.

If you use in straight table or pivot table in expression part, you can use column(2) - column(1). Which post give helps to u, can u mark as helpful answer.

Hope it helps,