topic Re: table : evolution between 2 dates in QlikView

table : evolution between 2 dates

fabricecallegari — Tue, 11 Aug 2015 17:59:59 GMT

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 ?

Re: table : evolution between 2 dates

sinanozdemir — Thu, 13 Aug 2015 22:41:37 GMT

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.

Re: table : evolution between 2 dates

fabricecallegari — Sat, 15 Aug 2015 20:37:12 GMT

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...

Re: table : evolution between 2 dates

marcus_sommer — Sun, 16 Aug 2015 08:55:59 GMT

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

Re: table : evolution between 2 dates

fabricecallegari — Sun, 16 Aug 2015 17:23:52 GMT

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...

Re: table : evolution between 2 dates

sinanozdemir — Mon, 17 Aug 2015 16:27:34 GMT

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

Re: table : evolution between 2 dates

sinanozdemir — Mon, 17 Aug 2015 17:12:08 GMT

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

Re: table : evolution between 2 dates

Fri, 21 Aug 2015 13:05:30 GMT

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

Re: table : evolution between 2 dates

sinanozdemir — Fri, 21 Aug 2015 13:35:29 GMT

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