topic Re: Dimensions for Week over Week comparison in a Bar Chart in QlikView

Dimensions for Week over Week comparison in a Bar Chart

Mon, 20 Jun 2016 20:26:15 GMT

Hello,

I am am creating a bar chat showing a Week over Week comparison. My Expression 1 was created to only show data for the current week. The second expression was created to only show data for the prior week.

However, I have my x-axis as CC_Week and it does not show the week number. I have also tried to change out the dimension for Week(CC_Date), but that does not seem to work as well. Please see my expressions below:

Current Week:

=Sum({1<CC_Date = {">=$(=Date(WeekStart(Max(CC_Date)), 'M/D/YYYY'))

<=$(=Date(WeekEnd(Max(CC_Date)), 'M/D/YYYY'))"}>} [Answered])

Prior Week:

=Sum({1<CC_Date = {">=$(=Date(WeekStart(Max(CC_Date),-1), 'M/D/YYYY'))

<=$(=Date(WeekEnd(Max(CC_Date),-1), 'M/D/YYYY'))"}>} [Answered])

I would think that my final result would show two dates (or week numbers) on the x-axis.

Re: Dimensions for Week over Week comparison in a Bar Chart

sunny_talwar — Mon, 20 Jun 2016 20:47:03 GMT

Can you show the image of what you have right now?

Re: Dimensions for Week over Week comparison in a Bar Chart

Mon, 20 Jun 2016 20:52:44 GMT

Re: Dimensions for Week over Week comparison in a Bar Chart

sunny_talwar — Mon, 20 Jun 2016 21:07:51 GMT

So you want to show the week number on the axis? That is what is missing?

Re: Dimensions for Week over Week comparison in a Bar Chart

Mon, 20 Jun 2016 21:18:36 GMT

You can use "value list" as a calculated dimension, and a function "pick".

See the img and qvw

Edit: You can also use variables or expressions on value list

Re: Dimensions for Week over Week comparison in a Bar Chart

Tue, 21 Jun 2016 12:50:33 GMT

yes that is correct

Re: Dimensions for Week over Week comparison in a Bar Chart

Tue, 21 Jun 2016 13:02:24 GMT

My current expressions are:

Current Week:

=Sum({1<CC_Date = {">=$(=Date(WeekStart(Max(CC_Date)), 'M/D/YYYY'))

<=$(=Date(WeekEnd(Max(CC_Date)), 'M/D/YYYY'))"}>} [Answered])

Prior Week:

=Sum({1<CC_Date = {">=$(=Date(WeekStart(Max(CC_Date),-1), 'M/D/YYYY'))

<=$(=Date(WeekEnd(Max(CC_Date),-1), 'M/D/YYYY'))"}>} [Answered])

Can you assist me in how I would add the Pick(RowNo(), to the above?

Thanks!

Re: Dimensions for Week over Week comparison in a Bar Chart

Tue, 21 Jun 2016 15:20:49 GMT

Of course I can!

Your dimension would be:

Valuelist("Current Week", "Prior Week")

Your expression would be:

pick(Rowno(),

Sum({1<CC_Date = {">=$(=Date(WeekStart(Max(CC_Date)), 'M/D/YYYY'))

<=$(=Date(WeekEnd(Max(CC_Date)), 'M/D/YYYY'))"}>} [Answered]),

Sum({1<CC_Date = {">=$(=Date(WeekStart(Max(CC_Date),-1), 'M/D/YYYY'))

<=$(=Date(WeekEnd(Max(CC_Date),-1), 'M/D/YYYY'))"}>} [Answered])

)

The structure is:

Pick(Rowno(),

first_expression,

second_expression

)

if you have more than 2 expression, just write them, don't forget the "," at the end