topic Re: Stacked Chart formatting in QlikView

Stacked Chart formatting

Harks — Wed, 25 Nov 2020 16:16:04 GMT

Hi!

I have a chart that is stacked and values split into categories. Ideally I'd like the format of the sum of the categories to change depending on their value. So if a sum of a category is over 150000 then the value display 150K but if another category on the same chart moves into the millions for it to display a million maker eg. 1100000 then it to display 1.1M.

At present I can put the stacked formats into a single format, but not multiple format changes only - if I attempt to apply a formula it takes the value sin a literal sense and doesn't just change the format method - I've attached an image with my graph format at present - Ideally anything under 1 million I'd like to display in thousands - just the (k).

The formula at present is this: "num(sum(fabs([count]))/1000000,'##.0m')"

If I put an if statement around it - it looks at the formula in a literal sense, and not just the formatting. I'm thinking maybe something along the lines of a variable string to add the M or K on, but I'm still stuck with the other stacked values becoming out of sync.

Any help or pointers in the right direction would be great!

Thanks

Re: Stacked Chart formatting

Anil_Babu_Samineni — Sun, 25 Nov 2018 13:19:03 GMT

Try this?

If(Len(sum(fabs([count]))/1000000)=10, Num(sum(fabs([count]))/1000000),'##.0 M'), If(Len(sum(fabs([count]))/10000)=5, Num(sum(fabs([count]))/10000),'##.0 K')))

Re: Stacked Chart formatting

Harks — Mon, 26 Nov 2018 12:47:39 GMT

Hi Loveifail,

Thanks for having a try at this! I did attempt this (couple to many brackets in your formula, but I get where you are going with it), however the formula is looking at it from a very literal point of view and excludes results from the chart that don't meet the requirement of the count - so if I have a category (EG - Yellow that's 12 million and a category EG - blue - that's only 125000) it excludes the 1.2 million rather than change the format of the number. I only need a format change - I don't want to exclude results, I need the results format to change on selections made - I just need the end format to be different on the same chart for ease of viewing - in some instances I have results where I'm in the millions versus results in the high tens of thousands - and I want the user to be able to easily see numerically that there's 12.5K blue and 1.2Mil yellow.

I don't think its actually possible on the same chart - very happy to be shown different - but I think stacked number formats all need to be the same.

Massively appreciate you taking the time to look at this!

Thanks

Harks