Expression help ?

[#1]

Hi all

I need to calculate NET SALES based on VAT rate. But VAT is changing year to year on a particular date. Net sales = Gross sales - VAT.

Only 'Gross Sales' field available in database. So I have created a YEAR dimension extracted from DATE field. YEAR values are 2004 , 2005,… 2011.

VAT details:

Year VAT% With effect from

2011 20% 04/01/2011

2010 17.5% 01/01/2010

2009 15.0% 01/12/2008

2008 17.5% -------------

2007 17.5% -------------

2006 17.5% -------------

2005 17.5% -------------

2004 17.5% -------------

If I click year 2011…that should show the 'Net sales' based on VAT rates 20% and 17.5% .. because VAT has changed on 04^th Jan 2011. Before that, it was 17.5%

Can someone tell me the code to write in expression ?

Many thanks

[vgutkovsky]

The expression itself will be really simple, you just need to implement this properly in your data model. I'm assuming you have a calendar dimension with years in it. Do a LEFT JOIN onto that table to bring in VAT%. Because of QV's associate data model, each sales transaction will then be associated with a particular VAT% because it is associated with a date. Your expression would then be simply: (1-VAT)*sum(Sales)

Regards,

[Miguel_Angel_Baeyens]

Hello,

If your sales transactions table is related to your calendar, then the associative logic should do the rest. Using the following should work regardless of the year selected, if any:

Sum(Gross * VAT)

In case VAT is a percentage.

Hope that helps.

[#4]

Hi

Thanks for your replies.

I am still bit confusing. There's NO field available for VAT. So i have loaded data like this:

LOAD [BOOKING_DATE],

Year([BOOKING_DATE]) as year,

`GROSS_SALES`;

SQL SELECT `BOOKING_DATE`,

`GROSS_SALES`

FROM A;

VAT:

LOAD * INLINE [

year Vat

2004, 17.5

2005, 17.5

2006, 17.5

2007, 17.5

2008, 17.5

2009, 15.0

2010, 17.5

2011, 20.0

;

Now i got 'Vat' dimension. If i click year 2011, it will show the associative value 20.0 in Vat. But VAT has chaged on 04^th Jan 2011 ( 'VAT details' given above). From 1^st Jan 2011 to 3^rd jan2011, it was 17.5%. So If i click 01/01/2011 in BOOKING_DATE that should show 17.5 in vat. How can I manipulate this …based on BOOKING_DATE ?

Many thanks

[vgutkovsky · Accepted solution]

You need to join VAT on Booking Date, not on year. Unless you're very patient, I doubt you can do this with an inline load. Rather create a table with the following fields: BeginDate, EndDate, VAT. Then you can do either an intervalmatch using this table (see help file for more details) or simply a join as follows:

data:
LOAD
 [BOOKING_DATE],
 Year([BOOKING_DATE]) as year,
 `GROSS_SALES`;
SQL SELECT `BOOKING_DATE`,
`GROSS_SALES`
FROM A;
VAT_table:
LOAD
 BeginDate,
 EndDate,
 VAT
FROM myfile.qvd (qvd);
JOIN (VAT_table) LOAD [BOOKING_DATE] RESIDENT data;
final_VAT_table:
LOAD
 [BOOKING_DATE],
 VAT
RESIDENT VAT_table
WHERE [BOOKING_DATE]>=BeginDate and [BOOKING_DATE]<=EndDate
;
DROP TABLE VAT_table;

Regards,

[#6]

Thanks Vlad!

Your solution working fine for me...