formula neded

[#1]

I have weekly rates input sheet that will be fed to Qlikview in form of QVD. based on weekly rates i have to calculation needs to be done.

User will enter this info on weekly basis. Based on the input, qvd generator will append weekly rates into a qvd file. Qlikview dashboard will access these rates from table called Rates. Each calculation within chart now needs to be based on rates that falls in period 1 and week 1 (for exampe)
Period No	Week No	Canadian Dollar rate	US Dollar Rate	British Pound Rate
1	1	10%	10%	10%
1	2	20%	20%	20%
1	3	30%	30%	30%
1	4	40%	40%	40%
2	5	50%	50%	50%

Within Dashboard Period, Week No corresponds to set of dates in a month. For all those days above rate apply to all the calculations. How can make the formula. Let say for exampe i want to calculate:

Jan-01-2014  CanadianGood=$100%(rate)

now rate needs to fetch from rates qvd based on date it corresponds. How can i write a formula.

[arsal_90]

Do you have date in that so on basis of date field what you can do is that take max(date) and then take rates of that max rate

Let say

date                    US$ Rate

1-jan-2014               100

2-jan-2014                101

what max do is that take the max date on daily basis

[MK_QSL]

Can you provide sample data ... Rate and your Fact Table with say... 10-15 lines.

It is difficult to understand from your above question.

[#4]

User will go into weekly excel spreadsheet and enter new rates for that whole week like below.

Period No	Week No	Canadian Dollar rate	US Dollar Rate	British Pound Rate
1	1	1.5%	1%	0.6%
1	2	1.42%	1%	0.65%

Now based on that, my qlikview dashboard chart needs to calculate output. Chart looks like below.

Date                 Customer#     CustomerName     Candian$Amount     US$Amount        BritishAmount

--------------------------------------------------------------------------------------------------------------------------------------------------------

Jan-02-2014                                                       $20X(rate)               $20X(rate)          $20x(rate)
Jan-03-2014

Jan-04-2014

Jan-05-2014

Jan-06-2014

Jan-29-2014    (here Period1 Week2 rates will apply because Jan-29-2014 falls in Week 2)

Now based on user input in excel spredsheet, formula needs to determine what the rate is for that day. If Jan-02-2014 falls in PeriodNo1 and WeekNo1 then multiply $20 with the rate of that matching week from spreadsheet.

There is week#, Period# field in my dashboard.

[MK_QSL]

Alongwith Period and Week NO in rate table, add one more column with WeekStart Date and WeekEnd Date.

Now you can use IntervalMatch Function.

[maxgro]

If I understand

I think you have to transform your Rates table joning with a Calendar and get a new Rates  table similar to

Date     Period No     Week No     Canadian ..............

(see below image)

Then your chart should be simple to create because of the association between Date (in new table Rate) and Date in Fact Table

But it's not clear how to generate the new Rates table:

why is Jan-29-2014  in Period1 Week2?

what's Period?

is this a correct Rates table?

[#7]

ok i have adjusted my input table to include week start and week end. how can i now use interval match to get results like dashboard chart below.

What will formula be for Canadian Sales, US Sales, Sales in Pound.

Weekly input table

Period No	Week No	Week Start	Week end	Canadian Dollar rate	US Dollar Rate	British Pound Rate
1	1	2-Jan-14	5-Jan-14	92.00%	100.00%	122.00%
1	2	6-Jan-14	12-Jan-14	95.00%	100.00%	127.00%
1	3	14-Jan-14	19-Jan-14	90.00%	100.00%	133.00%

Dashboard Chart.

Date	Customer #	Customer Name	Sales Total	Canadian Sales	US sales	Sales in Pound
Jan-02-2014	123	abc	100.00	$92.00	$100.00	$122.00
Jan-03-2014	124	abd	200.00	$184.00	$200.00	$244.00
Jan-04-2014	125	abe	300.00	$276.00	$300.00	$366.00
Jan-07-2014	126	abf	400.00	$380.00	$400.00	$508.00
Jan-18-2014	127	abg	500.00	$450.00	$500.00	$665.00

[MK_QSL]

I am outside and don't have laptop.  You can try below link.  If you not get success will do for you tomorrow

http://community.qlik.com/blogs/qlikviewdesignblog/2013/04/04/intervalmatch

[MK_QSL]

Use below script...

===================

Rate:

Load

  [Period No],

  [Week No],

  Date(Date#([Week Start],'D-MMM-YY')) as [Week Start],

  Date(Date#([Week end],'D-MMM-YY')) as [Week End],

  [Canadian Dollar rate],

  [US Dollar Rate],

  [British Pound Rate]

Inline

[

  Period No, Week No, Week Start, Week end, Canadian Dollar rate, US Dollar Rate, British Pound Rate

  1, 1, 2-Jan-14, 5-Jan-14, 92.00%, 100.00%, 122.00%,

  1, 2, 6-Jan-14, 12-Jan-14, 95.00%, 100.00%, 127.00%,

  1, 3, 14-Jan-14, 19-Jan-14, 90.00%, 100.00%, 133.00%,

];

Sales:

Load

  Date(Date#(Date,'MMM-DD-YYYY')) as Date,

  [Customer #],

  [Customer Name],

  [Sales Total]

Inline

[

  Date, Customer #, Customer Name, Sales Total

  Jan-02-2014, 123, abc, 100.00

  Jan-03-2014, 124, abd, 200.00

  Jan-04-2014, 125, abe, 300.00

  Jan-07-2014, 126, abf, 400.00

  Jan-18-2014, 127, abg, 500.00

];

Join IntervalMatch (Date) Load [Week Start],[Week End] Resident Rate;

Left Join (Sales) Load * Resident Rate;

Drop Table Rate;

==========================================

Now create a straight table

Dimension

Customer #

Customer Name

Date

Expression

Total Sale

SUM([Sales Total])

Sales in CAND

SUM([Sales Total]*[Canadian Dollar rate])

Sales in USD

SUM([Sales Total]*[US Dollar Rate])

Sales in GBP

SUM([Sales Total]*[British Pound Rate])

Hope this helps...