AsOfDate / Point in Time question!

[alexis]

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

[sujeetsingh]

try using expressions in spite of variables

[alexis]

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

[qlikpahadi07]

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.

[alexis]

This looks very very promising QlikPahadi07!

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

[qlikpahadi07 · Accepted solution]

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

[qlikpahadi07]

Hi Alexis,

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

[alexis]

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

[alexis]

QlikPahadi07

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

I cannot thank you enough.

best regards

Alexis