April 17, 2015

# 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.

By date: By tag: