Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Weird Display in Chart with Multiple Dimensions & Expression

I need to create a chart to show Revenue by Product, in comparison with Total/Average Plan through the year. Excel manages to present this decently.

Excel Chart.PNG

But I cannot do it with Qlik View. The data looks terrible and doesn't carry much business value. The Revenue breakdown cannot be shown with different Color. Total & Average Planned was not aligned to the time.

Qlik Chart.PNG

Any Guru has experience in managing dimension and expression in this case? I believe Qlik should be smarter than Excel. Appreciate any help.

1 Solution

Accepted Solutions
felipedl
Partner - Specialist III
Partner - Specialist III

Thing is, you cant show a Year value when your opening it by Year_Month.

My solution would be to create new fields to get the result by doing:

x:

LOAD Year,

    Month,

    Date#(Year &'_'&Month,'YYYY_MMM') as Year_month,

    Product,

    Rev,

    Planned

FROM

[Company Revenue.xlsx]

(ooxml, embedded labels, table is [Rev report]);

// sum of planned by year

left join

Load

Year,

sum(Planned) as [New Planned]

Resident x

group by Year;

// count of year_months by year, being possible to not have full years on dataz

left join

Load

Year,

count(Distinct Year_month) as [Count Planned]

Resident x

Group by Year;

That gets me:

sample.png

View solution in original post

7 Replies
felipedl
Partner - Specialist III
Partner - Specialist III

Hi Angie,


I've made the adjustments so it's possible to plot the same information.

You need to use set analysis to get the individual values for each type of product to create the stacked bar chart.

Final result is this:

sample.png

Or this, if you put the avg(Planned) to the secondary axis:

sample.png

Anonymous
Not applicable
Author

Hi Felip,

The set analysis works fine with Revenue by Products. However, the Average for each year should be a constant. In the Excel chart, the formula would be sum(total planned) by year divided by 12.

Can you suggest expression to replace avg function in this case? Big thanks.

felipedl
Partner - Specialist III
Partner - Specialist III

With the data you have, the avg of the planned wont be constant, due to the fact that you have multiple lines for each dimension, like so:

Year Month Year_month Product Rev Planned
2016Feb2016_FebSmartphone454524
2016Feb2016_FebAccessories557642
2016Feb2016_FebSmartphone586676
2016Feb2016_FebLaptop595686
2016Feb2016_FebLaptop618713

That's the data you have on the app for 2016_Feb and the avg will be 648.2.

You have to define your data on a different way to get a constant value.

Anonymous
Not applicable
Author

Thanks Felip. Is there a work around to keep the dimension the same? As the purpose of chart is to show the breakdown by month and average by Year.

I managed to set up the formula in Excel as attached. Is it possible to have a similar expression in Qlik?

felipedl
Partner - Specialist III
Partner - Specialist III

Thing is, you cant show a Year value when your opening it by Year_Month.

My solution would be to create new fields to get the result by doing:

x:

LOAD Year,

    Month,

    Date#(Year &'_'&Month,'YYYY_MMM') as Year_month,

    Product,

    Rev,

    Planned

FROM

[Company Revenue.xlsx]

(ooxml, embedded labels, table is [Rev report]);

// sum of planned by year

left join

Load

Year,

sum(Planned) as [New Planned]

Resident x

group by Year;

// count of year_months by year, being possible to not have full years on dataz

left join

Load

Year,

count(Distinct Year_month) as [Count Planned]

Resident x

Group by Year;

That gets me:

sample.png

Anonymous
Not applicable
Author

Thanks Felip for detailed explanation. I can get the correct chart now.

felipedl
Partner - Specialist III
Partner - Specialist III

Glad it helped .