topic Re: set analysis with if statement in QlikView

set analysis with if statement

userid128223 — Fri, 22 Jun 2012 20:54:59 GMT

Below statement gives me current total sales of all the companies despite any selection.

What I want to do is:

Give me total sales of currently selected company, excluding other companies, excluding any time line selection.

=if(PurchaseDate > AddMonths(today(),-12) this is true then sum(totalsales) - (total cost) excluding time period selection.

How can i do that.

Thx

Sat, 23 Jun 2012 05:25:46 GMT

should be something like this...

sum( {$<PurchaseDate ={'>$(=AddMonths(today(),-12) '}>} totalsales) -
sum({$<PurchaseDate ={'>$(=AddMonths(today(),-12) '}>} total cost)

if you are dealing with period, it's better to build a calendar master data, says from past 2 year until next 50 years.

with columns like MMM YYYY, Quarter, row numbering, YYYYMM in digit (simply YYYY*100 + MM)

row numbering will be very very useful and handly when u want to do past 12 month until prev month... etc....

userid128223 — Mon, 25 Jun 2012 18:36:32 GMT

your suggestion did not work.

Any other help.

Tue, 26 Jun 2012 05:47:30 GMT

Hi,

Suppose the date format for PurchaseDate is DD-MM-YYYY then

Create a variable vDate= date(AddMonths(today(),-12),'DD-MM-YYYY')

=sum( {<PurchaseDate ={">=$(vDate)",Year,Month}>} totalsales) -

sum( {<PurchaseDate ={">=$(vDate)",Year,Month}>} total cost)

Note- to exclude time period selections bypass Year and Month as i did in the expression.

Regards

Apurva