Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 04th Jan 2011. Before that, it was 17.5%
Can someone tell me the code to write in expression ?
Many thanks
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,
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,
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.
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 04th Jan 2011 ( 'VAT details' given above). From 1st Jan 2011 to 3rd 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
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,
Thanks Vlad!
Your solution working fine for me...