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

[chrismtb]

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?

[sunny_talwar]

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

[chrismtb]

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?

[sunny_talwar · Accepted solution]

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

[chrismtb]

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

[Anonymous]

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

 Thanks