Bar Chart percentage on row

[MZambo89]

Hi everyone.
my bar chart at the moment shows the relative "% of order" for each plan type against the whole total.
This is my set analysis at the moment: =Count(distinct order_code) / Count(total distinct order_code)

How can I make that to be evaluated against each row? For example, the 100% will be the total for each week and not the whole dataset. Is that achievable in Qlik sense? Many thanks.

[Oleg_Troyansky]

Hi,

For the record - this is not set analysis, this is just an expression, but that's just a matter of terminology.

To answer your question - yes, you can divide by weekly totals. Assuming that you have a field like Week in your calendar, you could add it to the TOTAL qualifier, like this:

Count(distinct order_code) / Count(distinct total <Week> order_code)

Cheers,

[MZambo89]

Hi Oleg,
yes, apologies for the misuse of the terminology. 
The complication is that I have an X-axis which is dynamic so the user can swap between weeks, days and months.
I did this using a variable and an if statement in the x-axis:

=if('$(vWDM)'='W', week_starting,

    if('$(vWDM)'='D', date(floor(order_creation_date)), 

    if('$(vWDM)'='M', monthyear ),), )

How can I incorporate that into the expression? Thank you

[MZambo89]

Also, that formula that expression would have not worked. 

[Oleg_Troyansky]

I'd create a variable that contains the dynamically selected dimension:

vDim = if('$(vWDM)'='W', 'week_starting', 

    if('$(vWDM)'='D', '=date(floor(order_creation_date))', 

    if('$(vWDM)'='M', 'monthyear ')))

and then use the same variable in the calculated dimension and in the TOTAL qualilfier:

Count(distinct order_code) / Count(distinct total <$(vDim)> order_code)

Also, I'd calculate the order date in the script, to keep this formula cleaner:

LOAD

...,

date(floor(order_creation_date)) as order_date,

...

Now, if something doesn't work they way you expected, please provide some details. 

Cheers,

[MZambo89]

it doesn't work as well. It becomes 1:1 so everything 100%.
I have done that already to transform the date in the load. At one point I was playing with the incremental load and I needed the timestamp so I had to put that there temporarily. 

[MZambo89]

I was doing a bit of test and it is very odd what is happening:
if I put on the x axis dimension = week_starting and then the set expesison as Count(distinct order_code)
/
Count(distinct total < week_starting > order_code) this will work.

If I put this in the  measure then it doesn't work as above but technically it should. right?

=if( '$(vWDM)'='W',
(Count(distinct order_code)/Count(distinct total < week_starting > order_code) ),
if('$(vWDM)'='D',
(Count(distinct order_code)/Count(distinct total < order_creation_date > order_code) ),
if('$(vWDM)'='M',
(Count(distinct order_code)/Count(distinct total < monthyear > order_code) ) ), ), )