2015

### Sankarmagesh Rajan's Blog

April 2015 Previous month Next month

# Selected region and others with two pieces of pie chart

Posted by Sankarmagesh Rajan Apr 28, 2015

Step1:

Create calculation as

Create parameter [Select Region] with list of regions.

Step2:

IIF([Select Region]=[Region],([Region]), "Others" )

Step3:

Create pie chart with newly created field and sales

Step4:

Show parameter control and select regions.

Refer Attached workbook.

# Running sum with bottom to top

Posted by Sankarmagesh Rajan Apr 28, 2015

Step1:

Drag month in Row shelf.

Step2:

Drag sales into text.

Step3:

Create calculation as

if first()==0 then total(SUM([Sales])) else

lookup(total(SUM([Sales]))-RUNNING_SUM(SUM([Sales])),-1) end

Step4:

Double click this new calculation.

Step5:

Change table calc, compute using order date

Now we can get bottom to top running sum.

# How to Maintain Running sum of sales after applying Filter?

Posted by Sankarmagesh Rajan Apr 17, 2015

When we selected any month in parameter then running sum will show correct sum in row.

Step:1

Create parameter with month like integer 1,2,3,etc

Step:2

Create calculation [Running sum of month total]

if [Month]=12 then (if last()=0  then (RUNNING_SUM(SUM([Sales])))end )

elseif [Month]=11 then (if last()=1  then (RUNNING_SUM(SUM([Sales])))end )

elseif [Month]=10 then (if last()=2  then (RUNNING_SUM(SUM([Sales])))end )

elseif [Month]=9 then (if last()=3  then (RUNNING_SUM(SUM([Sales])))end )

elseif [Month]=8 then (if last()=4  then (RUNNING_SUM(SUM([Sales])))end )

elseif [Month]=7 then (if last()=5  then (RUNNING_SUM(SUM([Sales])))end )

elseif [Month]=6 then (if last()=6  then (RUNNING_SUM(SUM([Sales])))end )

elseif [Month]=5 then (if last()=7  then (RUNNING_SUM(SUM([Sales])))end )

elseif [Month]=4 then (if last()=8  then (RUNNING_SUM(SUM([Sales])))end )

elseif [Month]=3 then (if last()=9  then (RUNNING_SUM(SUM([Sales])))end )

elseif [Month]=2 then (if last()=10  then (RUNNING_SUM(SUM([Sales])))end )

elseif [Month]=1 then (if last()=11  then (RUNNING_SUM(SUM([Sales])))end )

End

Step:3

Drag the month in to row.

Drag this [Running sum of month total] calculation into row

Step:4

Drag this [Running sum of month total] into filter and apply non-null values.

Step:5

Show parameter control and select months.

# Filtering Customer name using wildcard

Posted by Sankarmagesh Rajan Apr 17, 2015

Step:1

Create parameter with string data type and select all.

Step:2

Create Calculation [Wildcard Search]

CONTAINS("," + [Customer Parameter] + ",", "," + str(left([Customer Name],1)) + ",")

Step:3

Drag Customer name into row

Step:4

Apply [Wildcard Search] in filter and select true.

Step:5

Show Parameter control and type A,B,C and get names.

# Dynamic Dimension in view

Posted by Sankarmagesh Rajan Apr 16, 2015

Step:1

Create [Dim Parameter]

Step:2

Create calc as

Case [Dim Parameter]

When “Region” then [Region]

When “Product Category” then [Product Category]

End

Step:3

Drag this calculation into row

Step:4

Show parameter control and select dimensions.

# show current parameter date and show last whole year data in diff sheets

Posted by Sankarmagesh Rajan Apr 15, 2015

Step:1

Create parameter as

Step:2

Create two calculations

1.previous year data

if  datediff('year',[Order Date],[date1])=1 then "show" end

Note:

If you want to show fiscal year only then change formula like mar to last year april

IF datediff('month',[date1],[Order Date])>=-11 and datediff('month',[date1],[Order Date])<=0 THEN "show" else "hide" end

Step:3
Drag this into filter and select show.

This calc apply in filter with 4 sheets only

Step:4

2.current parameter date.

if  [Order Date]=[date1] then "show" end

Step:5

Drag this calc into 2 sheets want to show parameter date only.

Now apply all sheets in dashboard and show parameter control then select date want to show.

# Single Parameter with 4 KPI's

Posted by Sankarmagesh Rajan Apr 13, 2015

Step:1

Create one [Show Selection] and add list as Actual and Target.

Step:2

Create calculation as

case [Target parameter]

when "Actual" then [Actual Value]

when "Target" then [Target Value]

end

Step:3

Create 2nd calculation as

case [Actual parameter]

when "Actual" then [Actual Qty]

when "Target" then [Target Qty]

end

Step:4

Drag this two calculations into row.

Step:5

Show parameter and select Actual or target.

Note:

if you want to show multi measure in single axis

Drag measure name into column and measure value into row.

Remove other measure and add only calculated data.

# Dynamic measure selection and aggragation selection with parameter

Posted by Sankarmagesh Rajan Apr 13, 2015

# Step:1

Create parameter [Measure Parameter] list as sales, profit and Unit Price

# Step:2

Create parameter [Agg Measure Parameter] list as Sum and Avg

Step:3

Create calculation as follows [Measure Agg Calc]

IF [Measure Parameter] ="Sales" and [Agg Measure Parameter] ="SUM" then SUM([Sales])

ELSEIF [Measure Parameter] ="Profit" and [Agg Measure Parameter] ="SUM" then SUM([Profit])

ELSEIF [Measure Parameter] ="Unit Price" and [Agg Measure Parameter] ="SUM" then SUM([Unit Price])

ELSEIF  [Measure Parameter] ="Sales" and  [Agg Measure Parameter] =”Avg” then AVG([Sales])

ELSEIF [Display Selection] ="Profit" and [Agg Measure Parameter] ="Avg" then AVG([Profit])

ELSEIF [Measure Parameter] ="Unit Price" and [Agg Measure Parameter] ="Avg" then AVG([Unit Price])

END

# Step:4

Drag this [Measure Agg Calc] into column and drag any of dimensions into row

# Step:5

Show Parameter control and select Kpis.

# Dynamic Measure Parameter

Posted by Sankarmagesh Rajan Apr 13, 2015

# Step:1

Create parameter [Measure Parameter] list as Sales, Profit and Unit Price

# Step:2

Create calculation as follows [Measure Calc]

case [Measure Parameter]

when "Sales" then [Sales]

when "Profit" then [Profit]

when "Unit Price" then [Unit Price]

end

# Step:3

Drag this [Measure Calc] into coloumn and drag any of dimensions into row

# Step:4

Show Parameter control and select Kpis.

# Top & Bottom with other customers

Posted by Sankarmagesh Rajan Apr 13, 2015

By using Index( ) in calculated filed and drag that calculated field in rows shelf we can achieve this.Please find the attached twbx.I hope this solves your problem

Step1: Create calc field called index ie index( )

Step2: Drag the customer name and calc field index into row shelf.

Right click customer name descending and sort via field [sales]

Step3: create [Top N] parameter with integer data type

Step 4:Create calc field called top10? Title Top 10 calculations – Top10?

if [Index]<=[Top N] THEN "Top N"  ELSE IF [Index]>=SIZE()-[Top N]

THEN  "Bottom n"  else “Others” end end

Drag calculation field Top 10? Into filter then select top n and bottom n.

Drag the new calculation into row.

show parameter control and type 10,20 etc

# How to make KPI donut charts

Posted by Sankarmagesh Rajan Apr 10, 2015

First Step we can create one calculation.

Step1:

We can create one calc  as one.

Step 2:

Create pie chart with product and sales.

Step3:

Drag this one calc into coloumn two times and select dual axis.

Step4:

Un check aggregation in analysis menu.

Step 5:

Step6:

Select first one field in marks section and increase size of pie.

step7:

Drag the Product into colour and sales into label.

Step 8:

Select another one calc in marks and drag the new calculated total sales into label

-select mark as circle

-change colour as white

-Select size and reduce size

see image

Donut chart attached.

By date: By tag: