topic Re: Bar Chart percentage on row in App Development

Bar Chart percentage on row

MZqlik — Fri, 06 Oct 2023 15:42:32 GMT

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.

Re: Bar Chart percentage on row

Oleg_Troyansky — Fri, 06 Oct 2023 17:31:34 GMT

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,

Re: Bar Chart percentage on row

MZqlik — Sat, 07 Oct 2023 09:56:14 GMT

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

Re: Bar Chart percentage on row

MZqlik — Sat, 07 Oct 2023 10:02:10 GMT

Also, that formula that expression would have not worked.

Re: Bar Chart percentage on row

Oleg_Troyansky — Sat, 07 Oct 2023 15:24:07 GMT

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,

Re: Bar Chart percentage on row

MZqlik — Sat, 07 Oct 2023 15:45:07 GMT

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.

Re: Bar Chart percentage on row

MZqlik — Sat, 07 Oct 2023 18:17:52 GMT

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