topic Re: AsOfDate / Point in Time question! in QlikView

AsOfDate / Point in Time question!

alexis — Mon, 07 Apr 2014 09:16:06 GMT

Hi - I am reposting this with a simpler example and simpler instructions in the hope that someone will be able to assist me with this issue that I have been having.

In the application attached I include just a few of the key components and I have simplified to make the problem more understood..

a) I have a table "CustomerPackageActivity" that has records that have a date field(ActivityDate)

b) When a user selects a date period (in ChurnCalendar), I want to calculate:

- No of records in the date range selected (Let's call this A)

- No of records that have a date less than the minimum selected (B)

- No of records that have a date less than or equal to the maximum date (C)

- The "churn ratio" which is a calculation involving A, B and C for the whole period (this works fine)

- The "churn ratio" for every month (or even week) in the period selected - this would involve calculating (A), (B) and (C) for each of the months in the range - Only the (A) part works for me!

This ratio should take into consideration other selections hence why I included the "CustomerPackage" table to test that the ratio changes when a package is selected... It's a kind of "Point in time" problem but I simply cannot solve it :-(

Any help would be very very welcome

Alexis

Re: AsOfDate / Point in Time question!

sujeetsingh — Mon, 07 Apr 2014 09:44:28 GMT

try using expressions in spite of variables

Re: AsOfDate / Point in Time question!

alexis — Mon, 07 Apr 2014 09:47:24 GMT

Hi Sujeetsingh - thank you for replying.

Would using expressions instead of variables (containing the expressions) solve my problem?

I would welcome some input!

regards

Alexis

Re: Re: AsOfDate / Point in Time question!

qlikpahadi07 — Wed, 09 Apr 2014 13:26:16 GMT

Hi Alexis,

I am not Sure but I think I hit the jackpot . and if yes then nothing is impossible in Qlikview .

just confirm the output I will share the logic/Application with you shortly.

I am not sure but as per my knowledge it is not possible from front end you need to achieve this from Backend Script.

Re: Re: AsOfDate / Point in Time question!

alexis — Thu, 10 Apr 2014 22:31:48 GMT

This looks very very promising QlikPahadi07!

The numbers look spot on - how did you work this out?

Re: Re: Re: AsOfDate / Point in Time question!

qlikpahadi07 — Sun, 13 Apr 2014 04:37:32 GMT

Hi Alexis,

Sorry for late reply i was on holidays , joined today.

Cool Actually your output wasn't possible through front end because of Qlikview Behaviour, for <(DataTime Selection) you can't aggr that with Time Dimension as it automatically filters the Selection and make grpby with Dimensions.

What I did was I changed the data according to my output needed . Sometimes you get stuck but I was lucky enough with chilled beer and glad to help someone.

Attched qvw is binary of your application shared, just go through the script you will understand.

3 Cheers

Re: AsOfDate / Point in Time question!

qlikpahadi07 — Tue, 15 Apr 2014 15:10:09 GMT

Hi Alexis,

I hope your query is solved in that case can you close the thread with helpful/correct answer.

Re: AsOfDate / Point in Time question!

alexis — Tue, 15 Apr 2014 18:11:09 GMT

Hi QlikPahaddi07,

Once again I thank you very much for your reply. The scanty replies are because I am on holiday in Greece and only have occasional use of the internet. I will definitely have access on Thursday when I will be able to look at the code and of course award you with the correct answer and of course close the thread.

Speak soon

Alexis

Re: AsOfDate / Point in Time question!

alexis — Sun, 27 Apr 2014 16:30:50 GMT

QlikPahadi07

Thank you for the example file that solves the problem perfectly - your approach is ingenious.

I cannot thank you enough.

best regards

Alexis