Post crosstab error

[96sudeshnasen]

I am using the cross tab tool to get the status of certain contracts. 

For eg: Raw data

Status	Number
Closed	12
Open	2
In progress	9
Open	1
New	23

Cross tab

Closed	Open	In Progress	New
12	3	9	23

Now further down, I have some formulas to sum all of these : Total_Assigned: [Closed]+[Open]+[In progress]+[New]

The problem is that when I run the workflow at different times of the financial year, all these statuses might not be present i.e. we might not have New contracts or open contracts. Then he formula tool throws an error.

[atcodedog05]

Hi @96sudeshnasen 

Why dont you use a summarize to sum before cross tabbing.

[atcodedog05 · Accepted solution]

Hi @96sudeshnasen 

Another approach would be to use Total column option of cross tab

Aggregations selected

Scroll down

This dynamically selected all the status's number and sums it up.

Hope this helps 🙂 Feel to ask if you have any questions

If this post helps you please mark it as solution. And give a like if you dont mind 😀👍

[96sudeshnasen]

Slight correction:

The formula will not used [Closed] contracts in the sum. So if I use summarize before crosstab, I still have to filter for contracts other than the closed ones. Even then, have to do a hardcoding of the contract status in the filters.

Is there a better dynamic way? Summarize would surely work if I wanted the sum of all contract types

[atcodedog05]

Hi @96sudeshnasen 

Why dont you create another calculated field

Number (No closed)

If [status]="closed" then 0 else [number] endif

That way it will sum only other than closed.

[Qiu]

@atcodedog05 

I think @atcodedog05  is suggesting something like this, and you can keep the Cross Tab format as you need.

This is dynamic and will not throw error even your columns change.

[grazitti_sapna]

Hi @96sudeshnasen, why don't you  give this a try.

If this works for you kindly mark this post as solution.

Thanks.

[atcodedog05]

Hi @Qiu 

There is a option in crosstab itself to get the total column

@96sudeshnasen adding to @Qiu suggestion

Since we dont want closed in total we modify like this

35 doesnt include 12 closed

Hope this helps🙂