topic Re: formula neded in QlikView

formula neded

Sat, 12 Apr 2014 06:34:19 GMT

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.

Re: formula neded

arsal_90 — Sat, 12 Apr 2014 07:00:17 GMT

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

Re: formula neded

MK_QSL — Sat, 12 Apr 2014 07:18:30 GMT

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

It is difficult to understand from your above question.

Re: Re: formula neded

Sat, 12 Apr 2014 15:20:26 GMT

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.

Re: Re: formula neded

MK_QSL — Sat, 12 Apr 2014 15:44:07 GMT

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

Now you can use IntervalMatch Function.

Re: formula neded

maxgro — Sat, 12 Apr 2014 16:07:36 GMT

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?

Re: formula neded

Sat, 12 Apr 2014 17:42:57 GMT

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

Re: formula neded

MK_QSL — Sat, 12 Apr 2014 19:11:40 GMT

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

Re: formula neded

MK_QSL — Sun, 13 Apr 2014 08:11:54 GMT

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...