Previous Year YTD formula

if [Order Date] <= [date]-365
and DATEPART('year',DATEADD('month',12,[Order Date])) = DATEPART('year',DATEADD('month',12,[date]-365))
then [Sales]
end

Date is the parameter here..

Option 2:

sum(if year([Order Date]) = year([date])-1 and 

[Order Date] <= dateadd('year',-1,[date]) then [Sales] END)

Interview questions -12

1. Different phases considered while building the dashboard ?

2. Challenges faced while building the dashboard ?

3. How do you determine the timeline when you get any request to build a dashboard and justify

4. What are the criteria one should consider to build an efficient dashboard ?

YTD Formula

Option 1:

if
datediff('year', [Order Date], [date]) =0  and
month([Order Date]) <= month([date]) and
date( [Order Date]) <= date([date]) then [Sales] 
end

Here date is the parameter

Option 2:

if [Order Date] <= [date]

and DATEPART('year',DATEADD('month',12,[Order Date])) = DATEPART('year',DATEADD('month',12,[date]))
then [Sales]
end

Option 3: 

sum(if year([Order Date]) = year([dateinput]) and [Order Date] <= [date] then [Sales] END)

Color formatting along with text , color , KPI and shape for more than one measure

Say you have 7 or more measures and you need to show the KPI indicators along with the text values. This is a kind of challenge we faced

In order to successfully implement this scenario.

Here are the steps we followed in order to achieve it. We chose Dual axis method for this scenario.

For the time being i am showing it for one measure, you can do it for all of them in the same way.

1. Create two  calculated fields name it as ‘Color’ and ‘Color text’ respectively with value as 1.

2. Drag Color and Color text fields to Column shelf

3. By default it will be aggregated as SUM. So, Change it to Attribute.

4. Right Click on Color Text and make it as Dual Axis.(if you want you can also chose the               shapes instead of the default Abc text.

5. Now Create one more calculated field  say KPI-Sales to define the KPI values for each               measure.(Green > 50k, Red <10k, Yellow <30, like that).

6. When you see marks shelf you will notice the columns ATTR(Color) and ATTR(Color text).

7. Click on the Color Text 

8. Drag the calculated field KPI-Sales to the color shelf and assign/map the colors              

        accordingly.

9. Drag the measure (Sales) to the label shelf.

10. You will be able to see the Color and text for the Measure in your view.

11. Now you need to do the formatting of the name, fonts, column colors etc..

12. That’s all guys .. 

Enjoy :)

How to set the filter value default to none ..

Sometimes we may have a requirement to show the blank sheet in the dashboard.

User will have to give the inputs in the filter and then accordingly display the reports in the dashboard.

These are the steps you need to follow..

1.Click on the filter

2.select customize 

3.deselect the show ALL option

4.change the filter to single drop down mode

5.you will see the default value present in the filter

6.Now Edit the filter

7. In the general tab you see all the values 

8.you will also see ALL , NONE tabs..

9.click on None tab and

10.click on OK

11.Now your filter shows the default value as None.

Enjoy..:)

Tableau Starter Kit

1. Download the Tableau Software and Install it in your computer.
2. Access the Starter Kit Workbook  from the link http://www.tableau.com/learn/starter-kit
3. And  enjoy learning Tableau Desktop Tool within 20 days.
Source : Tableau Digital Team 

Data Blending -Key points