topic Re: Set Analysis with Period as date dimension in QlikView

Set Analysis with Period as date dimension

BootCam — Wed, 25 Nov 2020 16:16:04 GMT

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,

Re: Set Analysis with Period as date dimension

sunny_talwar — Fri, 09 Sep 2016 16:48:23 GMT

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

Re: Set Analysis with Period as date dimension

BootCam — Fri, 09 Sep 2016 16:59:18 GMT

Hi Sunny,

I am using this in the straight table expression.

Thanks,

Re: Set Analysis with Period as date dimension

Anil_Babu_Samineni — Fri, 09 Sep 2016 17:21:33 GMT

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

Re: Set Analysis with Period as date dimension

neelamsaroha157 — Fri, 09 Sep 2016 17:27:38 GMT

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.

Re: Set Analysis with Period as date dimension

BootCam — Tue, 13 Sep 2016 19:19:24 GMT

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,

Re: Set Analysis with Period as date dimension

BootCam — Tue, 13 Sep 2016 20:39:08 GMT

Hi Anil,

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

Thanks & regards,

Re: Set Analysis with Period as date dimension

BootCam — Tue, 13 Sep 2016 20:39:56 GMT

Hi Neelam, They both are in the same format.

Thanks,

Re: Set Analysis with Period as date dimension

neelamsaroha157 — Wed, 14 Sep 2016 13:20:51 GMT

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

Re: Set Analysis with Period as date dimension

BootCam — Wed, 14 Sep 2016 17:43:38 GMT

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.

Re: Set Analysis with Period as date dimension

neelamsaroha157 — Wed, 14 Sep 2016 18:11:00 GMT

You are welcome