Set Analysis with Period as date dimension

[BootCam]

Hi All,

Could you please tell me whats wrong with the calculation below? I am not getting values out of this calculation.

IF([Activation Period]=Max([Status Update Period]),

  sum({<[Type Code]={'A','B','C'}, [Status Code History]={'X','Y'}>}[Volume]),

  IF([Activation Period]<Max([Status Update Period]),

  sum({<[Type Code]={'A','B','C'}, [Status Code History]={'X','Y','Z'}>}[Volume])))

Note: Periods are in this format: "Jan-16" for example

Thanks,

Sk

[sunny_talwar]

Where are you using this? Can you may be provide a sample to look at this?

[BootCam]

Hi Sunny,

I am using this in the straight table expression.

Thanks,

Sk

[Anil_Babu_Samineni]

What are you trying to get the value. Might be this sorting there is no associative values. Have you check the testing. Or would you be able to provide sample

[neelamsaroha157]

It could be possible that [Activation Period] and [Status Update Period] have different data type and if not then try to convert both to numeric format, its easy to compare numeric format.

[BootCam]

The problem:

Status Code with "C5CC". Which is a cancellation of contracts. Status Code "AAAA" is Activation of Contract.

-> If a contract Activate and cancelled in the Same Year & Same month then we ignore that as a contract and

dont include that as revenue. But if it Activated and Cancelled in the same Year But in different month we consider

That Contract as revenue for the Activation month.

For example, If a contract activated(AAAA) on July 29th 2016 and cancelled(C5CC) on August 1st 2016, then we

Consider revenue for this contract for the month of July 2016.

What I tried to do is:

-> Derive the Activation Date from Status Update Date based on the Status Code (AAAA) and

Last Update Date from the Status Update Date based on taking the Max(Status Update Date)

-> Then Made Activation Period like Jul-2016 and Take the Last status period Jul-2016. Then Compare if the

Activation Period equals or Less than the Activation Period. If the Activation Period = Last Update period

then Dont consider revenue. But when Activation Period is less than Last update period then Consider Revenue.

IF([Activation Period]=Max([Last Update Period]),

  sum({<[Customer Type Code]={'X','Y','Z'}, [Status Code History]={'*'}-{C5CC}>}[Price]),

  IF([Activation Period]<Max([Last Update Period]),

  sum({<[Customer Type Code]={'X','Y','Z'}>}[Price])))

Please let me know where I am doing wrong. Also please see the attach file.

Thanks,

Sk

[BootCam]

Hi Anil,

Please see my last response. I provide a sample of my application.

Thanks & regards,

Sk

[BootCam]

Hi Neelam, They both are in the same format.

Thanks,

Sk

[neelamsaroha157]

Hi Sk,

You can put this calculation in the script.

Try creating a flag If Year(Contract Activation Date) = Year(Contract Cancelled date) and Month(Contract Activation Date) = Month(Contract Cancelled date) then 1 else 0.

And then in front end you can use this flag in your set analysis.

Revenue = Sum({<Flag = {0}>} Revenue Field)

Provide the sample data if you need more clarification.

Hope this helps...

Thanks

[BootCam]

Its Awesome Neelam! Thank you very much for your reply. I think your solution is correct. Even though i am getting almost 2.5% difference from my target KPI but i think that is coming from the Data Model. Have to check the data model.

Thanks again.

Sk