Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 
MZambo89
Contributor III
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.

MZambo89_0-1696606913932.png

 



Labels (1)
6 Replies
Oleg_Troyansky
Partner Ambassador/MVP
Partner Ambassador/MVP

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
Contributor III
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
           



MZambo89
Contributor III
Contributor III
Author

Also, that formula that expression would have not worked. 

Oleg_Troyansky
Partner Ambassador/MVP
Partner Ambassador/MVP

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
Contributor III
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. 

MZambo89
Contributor III
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) ) ), ), )