Skip navigation
2015

Sankarmagesh Rajan's Blog

April 2015 Previous month Next month

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.

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.

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

 

Untitled.png

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.

Step:1

Create parameter with string data type and select all.

Untitled.png

 

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.

Step:1

Create [Dim Parameter]

Untitled.png

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.

Step:1

Create parameter as

Parameter.PNG

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.

Step:1

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

agg measure.png

 

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.

Step:1

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

 

measure para.png

 

Step:2

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

 

agg measure.png

 

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.

Step:1

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

measure para.png

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.

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

First Step we can create one calculation.

Step1:

We can create one calc  as one.

1.png

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:

Remove axis headers

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

1.png

Untitled.png

Donut chart attached.