Sorting based on selection criteria 1

[vega83]

Friends If i slected todays date my data should sortup as below, how can i achive this?
MOnth
July09
June09
May09
APR09
Mar09
Feb09
jan09
Dec08
Nov08
Oct08
Sep08
Aug08
jul08
Thanks so much,
V

 

[vega83]

Also is my first solution okay?, create manula headers and I will create running totals based on each month/group and I can do grand total on report summary.

 

[lbass]

Sure, you can do a manual crosstab, it's just more work. If you use an inserted crosstab, just add the date field directly {command.Added_Dt} to the crosstab and while it is highlighted->group options and choose "on change of month". Then you can right click on the field in preview->format field->date and format it as you wish.

-LB

 

[vega83]

LB, you are right Manual Cross Tab is too much work, I have like 5 different summary totals that I need to get. I will insert the date into Column and those 5 formulas in Summariezed fields. What shall i select as Rows?

I Should get like this
July June May ......
Process 45 6 0
Unprocess 10 8 6
Pending 11 1 4
Toatal 66 15 10

My formulas are all if else conditions and I want to count only those with distinct UPI numbers

Data is details

UPI date status ID
100 2009-05-06 10 656
100 2009-06-06 10 744
203 2009-06-11 22 55
203 2009-07-06 10 59
203 2009-07-01 0 57
My formulas are
If STats = 10 then 1 else 0. But I will get extra count , how can it count on chnage of UPI without using running totals in Cross Tab?

Thanks a lot.
V

 

[vega83]

Also when i was doing manal method?, I had create 12 running totals for each formula(12 months) , is there any other short path?, thanks!

 

[lbass]

What are these five summaries you are referring to? If the status field relates to the text you show in your example, then you should use a formula like this for your row field:

if {command.status} = 10 then
"Pending" else //or whatever 10 =
if {command.status} = 22 then
"Process" else
//etc.

Add this as your row field. Not sure what it is you are trying to count, so I'm unsure of the summary, but it might be a distinctcount of UPI from what you are saying.

-LB

 

[vega83]

Your right LB, I am trying to do distinct count, if Stat - 10 then its Process or if 20 Unprocess, other is Pending.

Shall i manually enter the text in total column in Cross Tab
?
I am caculating total no of process.unprocess, etc for each month In this way
July June May ......
Process 45 6 0
Unprocess 10 8 6
Pending 11 1 4
Toatal 66 15 10

All the 5 formulas have the condtions
for Process
If field = 10 then 1 else 0, so on.
Thanks a ton,
V

 

[lbass]

I thought you were inserting a crosstab, NOT creating a manual crosstab. If you are inserting a crosstab, you do not need to use conditional formulas for your summaries. You would just insert a distinctcoutn on the field you want to count once per month per status--whatever that is.

-LB

 

[vega83]

LB,
I have inserted the cross tab, In columns I have added date and summarizing the fomrulas. What shall I give in Row column?, to display like below in rows, shall I enter the words manually?
Process
Unprocess
Pending
Toatal

Thanks so much

 

[vega83]

LB,
Also I have average process application,
date applied - date approved in days/ total no of applications

How can i Use this running totals?

 

[lbass]

Please see my previous post where I showed you a formula for the row field.

I don't see how those summaries relate to your month column. If you have complex summaries, then you SHOULD use a manual crosstab. I don't have enough info to help.

-LB

 

[vega83]

My sample data
UPI applied date approved data days
101 2009/07/29 2009/07/30 1
102 2009/07/29 2009/07/31 2
103 2009/07/29 2009/07/31 2
103 2009/07/29 2009/07/31 2


I have to add 1+2+2 = 5/3(distinct UPIs) , how can i Isert this in Cross Tab?

 

[lbass]

As I said, you are better off using a manual crosstab in this case.

-LB

 

[vega83]

Thanks LB, Cross Tab is getting complicated, I have 10 formulas which means 10 for each month will be 120 running totals, is there any limit how many running totals I can use in Crystal?
Thanks,
V

 

[lbass]

Why 10 per month? I thought you had five summaries? Are you looking for summaries at more than one level? You could potentially be using conditional formulas instead of running totals (if you don't have duplicating values).

-LB

 

[vega83]

LB, since I have to show for last month horizontally. And I have
10 type of summaries one for processed, one for unprocessed,one for ageso on,. SO I am Planing to create these summaries for each month. I need to create 10 different summaried for each month right?
For example Below
July June May April ........

Household approved 10
declined 4
Age>60 23
Child<6 67

 

[vega83]

Oh By the way I do have duplicating values, I have to summaries only once for each UPI.

 

[vega83]

Can Anyone Confirm if there is a limit on Running Totals?

 

[vega83]

LB,
Even my Manula corss Tab is not working,
I created formula Month no:
WhileReadingRecords;
If MOnth({Command.DECISION_DATE}) = Month({?Report_Date})
and
Year({Command.DECISION_DATE}) = Year({?Report_Date})
then 1
else if MOnth({Command.DECISION_DATE}) = Month(DateAdd("m",-1,{?Report_Date}))then 2
else if MOnth({Command.DECISION_DATE}) = Month(DateAdd("m",-2,{?Report_Date}))then 3
else if MOnth({Command.DECISION_DATE}) = Month(DateAdd("m",-3,{?Report_Date}))then 4
else if MOnth({Command.DECISION_DATE}) = Month(DateAdd("m",-4,{?Report_Date}))then 5
else if MOnth({Command.DECISION_DATE}) = Month(DateAdd("m",-5,{?Report_Date}))then 6
else if MOnth({Command.DECISION_DATE}) = Month(DateAdd("m",-6,{?Report_Date}))then 7
else if MOnth({Command.DECISION_DATE}) = Month(DateAdd("m",-7,{?Report_Date}))then 8
else if MOnth({Command.DECISION_DATE}) = Month(DateAdd("m",-8,{?Report_Date}))then 9
else if MOnth({Command.DECISION_DATE}) = Month(DateAdd("m",-9,{?Report_Date}))then 10
else if MOnth({Command.DECISION_DATE}) = Month(DateAdd("m",-10,{?Report_Date}))then 11
else if MOnth({Command.DECISION_DATE}) = Month(DateAdd("m",-11,{?Report_Date}))
then 12
else if
MOnth({Command.DECISION_DATE}) = Month(DateAdd("m",-12,{?Report_Date}))
and
Year({Command.DECISION_DATE}) = Year({?Report_Date}) -1
then 13


Then in runing toal I am summing up the formula on chnage of UPI and rest on formul
month no <> 1 for first month and so on. ANy help?

 

[vega83]

I have used in evaluate section fomula below
{Command.UPI} <> next({Command.UPI})
and {@Month_No} = 1, it works!. But let me know if there is better method.

 

[lbass]

I would use:

(
onlastrecord or
{Command.UPI} <> next({Command.UPI})
) and
{@Month_No} = 1

-LB