How to convert to 100% Stacked Bar chart

[yooyoo]

Hi there, I am new to Qlik and my authority is limited so I can't load the data editor. 

I am trying to set a 100% stacked bar to see gross order % in terms of different sales types. I have checked YouTube videos online and it seems I need to revise the expression like-  Sum(Sales)/Sum (total<Customer Name> Sales). However, I don't think total can be used for my case, as I don't have a field name <Month>. Error in expression : > expected appeared. 

Wondering if anyone can give me some advice. It's very frustrating that I feel like I need to download data to Excel and calculate by myself :(((((

Measure: 

Gross order = count(distinct(if([Contract Status]<>'Deleted' or [Contract Status]<>'Unapproved' or [Contract Status]<>'Unapproved never received',[Order Number])))

Dimension: 

Month=if([Order Date]>='01/01/2023',if([Order Date]<today(), monthname([Order Date])))

Sales Type = [Sales Type]

[marksouzacosta]

Hi @yooyoo,

Let's start cleaning up your code.

For Dimensions use only fields, nothing calculated. Avoid that as much as possible. This is not recommended. So, your fields will be:
Sales Type
Order Month Name *

* Create this field in your Load Script using the expression: monthname([Order Date]) AS [Order Month Name]

For your measure, avoid using If statements. Use Set Analysis instead:

Count({< [Contract Status] -= {'Deleted','Unapproved','Unapproved never received'}, [Order Date] = {">=01/01/2023<=$(=Today())"} >} Distinct [Order Number])

I'll check the Stacked 100% bar later using those expressions.

Regards,

Mark Costa

[marksouzacosta]

So for the Stacked bar, your measure will look similar to this:

{< [Contract Status] -= {'Deleted','Unapproved','Unapproved never received'}, [Order Date] = {">=01/01/2023<=$(=Today())"} >} Count(Distinct [Order Number]) / Count(TOTAL <[Order Month Name]> Distinct [Order Number])

 Pay special attention to the Set Analysis that is now outside the Measure expressions - meaning it will affect all the measures after it - and the key word TOTAL with the Dimension field that you are aggregating with.

Your bar chart should looks like this:

One quick note, I always recommend studying your numbers in table charts, splitting your measure into multiple ones, so you will see how the numbers are working for each part of your "bigger measure":

So, for the table chart, the measures will be:

{< [Contract Status] -= {'Deleted','Unapproved','Unapproved never received'}, [Order Date] = {">=01/01/2023<=$(=Today())"} >} Count(Distinct [Order Number])

{< [Contract Status] -= {'Deleted','Unapproved','Unapproved never received'}, [Order Date] = {">=01/01/2023<=$(=Today())"} >} Count(TOTAL <[Order Month Name]> Distinct [Order Number])

{< [Contract Status] -= {'Deleted','Unapproved','Unapproved never received'}, [Order Date] = {">=01/01/2023<=$(=Today())"} >} Count(Distinct [Order Number]) / Count(TOTAL <[Order Month Name]> Distinct [Order Number])

Regards,

Mark Costa

[yooyoo]

Hello Mark,

Thank you so much for your guidance. 

Since my authority is limited, I cannot create a field in the Load Script. 

That's why I was using if statement in dimension:((( 

*quote*

Month=if([Order Date]>='01/01/2023',if([Order Date]<today(), monthname([Order Date])))

*unquote*

Wondering if there is any chance for me to get the same result without loading script:((

[marksouzacosta · Accepted solution]

I'm afraid this is not possible, unless someone here in the Community figures out a way to do it.

Can you request someone to add this field for you?
Another possible alternative is to duplicate the current app and change the Load Script to Binary Load the original App. This will allow you to modify the Data Model in any way you need.