Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
Can you provide sample data ... Rate and your Fact Table with say... 10-15 lines.
It is difficult to understand from your above question.
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.
Alongwith Period and Week NO in rate table, add one more column with WeekStart Date and WeekEnd Date.
Now you can use IntervalMatch Function.
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?
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 |
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
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...