cancel
Showing results for
Did you mean:
Contributor III

## Bar Chart percentage on row

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.

Labels (1)
• ### General Question

6 Replies

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,

Contributor III
Author

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

Contributor III
Author

Also, that formula that expression would have not worked.

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:

...,
date(floor(order_creation_date)) as order_date,
...

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

Cheers,

Contributor III
Author

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.

Contributor III
Author

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