topic Re: Week On Week comparison (with set analysis??) to show increase in QlikView

Week On Week comparison (with set analysis??) to show increase

chrismtb — Thu, 11 Aug 2016 09:16:28 GMT

Hi,

I have looked through the forums and managed to partially solve my problem - I think however I have an opportunity to improve my solution for my users.

My data set currently comprises of the following information (example below):

Login ID	Date
1	01/01/16
2	01/01/16
3	01/01/16
2	02/01/16
3	03/01/16
4	03/01/16

My aim was to create a week on week comparison of the increase in users for this I ended up creating, importing and using an AsOf table which allowed me to use the following expression to create a chart:

=if([AsOfWeek]<Week(Now(2)),count(DISTINCT(User)))

this produced a chart looking something like this:

which answers my original question.

HOWEVER now I would like to make it much easier to see the increase. I want to split the graph into stacked bars where the lower bar shows the baseline from the previous week and the upper bar represents the increase

i.e.

The change obviously requires two expressions and no doubt will involve aggregation / set analysis but that is where I start to reach my limit on knowledge - any suggestions?

Re: Week On Week comparison (with set analysis??) to show increase

sunny_talwar — Thu, 11 Aug 2016 10:03:06 GMT

Would you be able to provide the sample you have been working with? Might be easy if we can also play around with it?

Re: Week On Week comparison (with set analysis??) to show increase

chrismtb — Thu, 11 Aug 2016 11:37:48 GMT

Sunny T,

An example QVW attached as suggested.

Apologies - I have added the data straight from my actual data-source as in-line tables so there is a lot in there... but it was quicker to add it all.

Also to note as these are in-line tables I have pre populated the Week No. conversion of the date although I just handle this as a calculation in the load script in my proper file.

Hope this gives enough context now?

Re: Week On Week comparison (with set analysis??) to show increase

sunny_talwar — Thu, 11 Aug 2016 11:45:45 GMT

Check the attached

Expression 1:

=Above(if([AsOfWeek]<Week(Now(2)),count(DISTINCT(User))))

Expression 2:

=if([AsOfWeek]<Week(Now(2)),count(DISTINCT(User)))-Above(if([AsOfWeek]<Week(Now(2)),count(DISTINCT(User))))

Re: Week On Week comparison (with set analysis??) to show increase

chrismtb — Thu, 11 Aug 2016 11:56:34 GMT

Great thanks Sunny T - as so often proved to me the answers are right there as long as you know the syntax to use!

Re: Week On Week comparison (with set analysis??) to show increase

Anonymous — Wed, 14 Nov 2018 21:57:42 GMT

Will I be able to choose a week in the past as "AsofWeek" in which I want to compare the data for?

Thanks