topic Expression help ? in QlikView

Expression help ?

Thu, 24 Mar 2011 16:04:25 GMT

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

Expression help ?

vgutkovsky — Thu, 24 Mar 2011 16:22:06 GMT

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,

Expression help ?

Miguel_Angel_Baeyens — Thu, 24 Mar 2011 16:22:16 GMT

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.

Expression help ?

Fri, 25 Mar 2011 15:16:40 GMT

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

Expression help ?

vgutkovsky — Fri, 25 Mar 2011 17:07:01 GMT

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,

Expression help ?

Fri, 01 Apr 2011 09:04:42 GMT

Thanks Vlad!

Your solution working fine for me...