22 Replies Latest reply: Sep 12, 2014 9:50 AM by Manish Kachhia

# Set analysis problem

hi QV,s

Can you plz Tell me this Expression in Set analysis

I have CMDBCI table.

Req:I want count of CmDBCInames for current month/Count(CmdbCi Names till Previous month)

harish

• ###### Re: Set analysis problem

What is the format of your date field?

• ###### Re: Set analysis problem

hi manish,

format of your date field:DD-MM-YYYY

• ###### Re: Set analysis problem

Have you created a Master Calendar?

Also,

What is the meaning of till Previous Month? You want starting from your first date from database to previous month end?

Count(CmdbCi Names till Previous month)

Please clarify little more on above two questions, which will make easy to answer.

• ###### Re: Set analysis problem

you are Correct,I want starting from my  first date of  database to previous month end.

I have created Master calendar

• ###### Re: Set analysis problem

Can you provide sample data base or apps?

• ###### Re: Set analysis problem

For Previous Month End

For Current Month

=SUM({<Month = {'\$(=Month(Today()))'},Year = {'\$(=Year(Today()))'}>}Amount)

You can change the field names according to your application...

• ###### Re: Set analysis problem

Hi Manish

It is simpler

MonthStart(Today())-1

than

results are the same

salutes

• ###### Re: Set analysis problem

Hi Manish,

The Expression for Prevous Month is giving only Previous month Count.I need total count ( from starting date of my database )

Expression for Previous Month:

=Count({<[Fiscal Month]={'<=\$(VarPrevFinMonth)'>}cmdbci_name)  it is not Working.

Regards,

Harish

• ###### Re: Set analysis problem

Hi,

Expression for previous Month End:

=Count({<[Fiscal Month]={"<=\$(=VarPrevFinMonth)">}cmdbci_name)

Regards

• ###### Re: Set analysis problem

hi,

It is not working .It results 0 value

• ###### Re: Set analysis problem

Hi,

check in text box what is  your variable VarPrevFinMonth evaluating?

Sometime it convert your date to number

also check Your Fiscal Month format in list box

Regards

• ###### Re: Set analysis problem

Hi Max,

I have checked VarPrevFinMonth is evaluating in text box .Month Format is MMM(Jan)

and Fiscal month format also same  MMM(Jan)

• ###### Re: Set analysis problem

Hi,

If possible post your sample apps?

One more thing if you want last month then add Year Field also in your set analysis.

Like,

Count({<[Fiscal Month]={"<=\$(=VarPrevFinMonth)",[Fiscal Year]={"<=\$(=VarYear)">}cmdbci_name)

Regards

• ###### Re: Set analysis problem

Hi

Instead of using month field, try with date field in set analysis for calculating from start date to end of previous month.

• ###### Re: Set analysis problem

hi ,

Can u please tell me how to get data till Previous Month end.

do you have any example Expressions or Conditions?

Regards,

Harish

• ###### Re: Set analysis problem

Hi

I am using this Date field,but still i m getting 0 value

• ###### Re: Set analysis problem

Hi

Expression looks correct..

Did u please confirm that CIDate is also "DD-MM-YYYY" format or its in "MM-DD-YYYY" format?

• ###### Re: Set analysis problem

hi,

CIDate format is 'DD-MM-YYYY'

• ###### Re: Set analysis problem

Hi

Can you provide a sample data in excel file ?

• ###### Re: Set analysis problem

hi,

PFA

Regards,

harish

• ###### Re: Set analysis problem

hi QV's ,

It is very urgent any suggestions plz reply

regards,

harish

• ###### Re: Set analysis problem

There is a problem in your cmdbci_Date field.. It is having multiple formats... First you need to make sure that cmdbci_Date field should be converted in a single date format...

Use below expression to do the same...

Alt(Date(Date#(cmdbci_Date,'DD-MM-YYYY')),Date(Date#(cmdbci_Date,'DD/MM/YYYY'))) as cmdbci_Date,

Now use below expression to get your expected result...