Qlik Community

Ask a Question

New to QlikView

If you’re new to QlikView, start with this Discussion Board and get up-to-speed quickly.

Announcements
Welcome to our newly redesigned Qlik Community! Read our blog to learn about all the new updates: READ BLOG and REPORTED ISSUES
cancel
Showing results for 
Search instead for 
Did you mean: 
Contributor III
Contributor III

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
Partner
Partner

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
Partner
Partner

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

Contributor III
Contributor III

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.

Partner
Partner

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.

Contributor III
Contributor III

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?

Partner
Partner

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

Contributor III
Contributor III

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

Partner
Partner

Glad it helped .