Hi All,
I need help from respected members of this community.
I have to make one report where one column have to take values from another excel. This other excel have some %values based on days of month(1-31) as rows and Countries as column. I need to us the % in this excel in my tableau report based on the date on filter. I have filter with daily, monthly and yearly for date part. For this filter i have used below calculation.
IF [Parameters].[Grnularity]='Yearly' THEN DATETRUNC('year',[Issuance Time])
ELSEIF [Parameters].[Grnularity]='Monthly' THEN DATETRUNC('month',[Issuance Time])
ELSEIF [Parameters].[Grnularity]='Daily' THEN DATETRUNC('day',[Issuance Time])
END
Here Issuance Time is the DateTime part of existing datasource.
Column that i need in my tableau report is as below:
Colum Heading: Previous Month WeightedCalculation: Previous month vol * appropriate weights
appropriate weight is the % values that i have to fetch from other excel that i have mentioned initially. I am attaching sample for the excel from where i need to pick value for the above calculation.
And also how can i get previous month vloume?
Any help would me much appreciated..
Hi Pawan,
One option would be to use blending to join the two data sets. Have you come across blending before? It's a bit like a left outer join (in join terms). Data from the secondary data source is aggregated which imposes some limitations, but it can also be handy. I've attached / described an example which may be similar to yours below, and included a link to the data blending info. Take a look and post back if it helps or not. If you including a sample workbook people will be able to get their teeth into any specific problems you're hitting with your data.
Ta,Steve.Data blending: Blend Your Data - Tableau
Example:
I used category instead of country but have a similar look up of % to your screenshot.
I added this spreadsheet as an extra data source as well as using sample superstore. I also pivoted the data above so that I have a row per day of month and category. That way I can define a relationship between the two data sources as follows:
Then I can build the view out as follows:
With a [Factored Sales] calc of: SUM([Sales]) * MIN([Sheet1 (day-product-perc-lookup)].[Perc])
Meaning that we see the sales factored by the relevant percentage in our look up sheet as illustrated for Furniture on 3/01/2018; 10% of sales of $1,593 is $159.
NB: The use of MIN when pulling in the percentage is just because we have to use an aggregation; the data is setup so that there should only be one record matching at any time anyway so the aggregation you pick is irrelevant.
I've attached the workbook I used in case it helps. Let us know!
