table : evolution between 2 dates

[fabricecallegar]

Hi,

I have a photo table with customers and category for each month.

For instance (2 months):

I would like to build a table which show the category of customer evolution between 2 dates (chosen from 2 lists) like that:

=> In 2015 06, 1 customer of cat 1 stays the cat 1 and 1 customer moves to cat 2 in 2015 07

=> In 2015 06, 1 customer of cat 2 stays the cat 2 and 1 customer moves to cat 1 in 2015 07

=> In 2015 06, 1 customer of cat 3 moves to cat 2 in 2015 07

How can I do that ?

[sinanozdemir]

Hi Fabrice,

Here is what I have:

I mostly handled everything in the backend:

And the expression is just Sum([Customer Count]):

I am also attaching the qvw.

Hope this helps.

[fabricecallegar]

Hi Sinan,

Thank you for your answer, it's exacly what I want!

but as you say, everything is performed in the script.

I'm looking for a dynamic solution because, in fact, I have not only 2 months and 5 customers but more than 2 years and several hundred thousands of customers...

[marcus_sommer]

I could imagine that with set analysis is a more flexible solution possible. This meant you don't used horizontal dimensions and builds for each needed column an own expression, maybe this is helpful for you: Set Analysis: syntaxes, examples.

- Marcus

[fabricecallegar]

Thank you Marcus for your link.

I know this documentation but I couldn't not build this formula.

I must count customer from the first month selected and compare their categories to the second month selected...

[sinanozdemir]

Hi Fabrice,

I have a bit more robust load script which you don't have to put any hard coded values or variable:

I still believe that it might be much easier to handle this in the back-end. Although, I will give a try to handle it in the front-end as well so stay tuned. I am also attaching the qvw for your reference.

Thanks

[sinanozdemir]

Hi Fabrice,

Here is my approach to handle things in the front-end. Mind you that the format is a bit different than what you asked for:

And the expression in the first pivot as follows:

And the expression in the second pivot as follows:

I am also attaching the qvw for this as well and hoping that this comes closer to what you are looking for.

Thanks

[#8]

Hello Sinan, i have a question about excel load into qlikview

~I need to load from an excel file

everytime it loads from the qvw, it needs to add a new row to a qvd ,

So first load will take one row from the qvw into qvd

and next relaod will be the second row from qvw to qvd

is there a way to do this ?

/ A

[sinanozdemir]

You can, but it will be easier if you have some sort of time stamp.

There is also the below discussion about achieving something similar by using DO WHILE loop:

Endless do loop: "do while ($(a) <= 3) "

You can also start a new discussion. Since I haven't done such thing, maybe there are some other users who have done something similar to this.

Thanks