Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Customer & Partners, DEC. 9, 11 AM ET: Qlik Product & Strategy Roadmap Session: Data Analytics REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
DOD
Contributor III
Contributor III

Straight table ignoring dimension - How to convert to line chart

Hello,

I have a set of data which basically has a series of transactions, each of which is tied to an account. Some of the accounts are revenue categories, while others are cost categories.

In this sample data set we have 3 groups, Manufacturing, IT and Marketing. Each of these groups has 1 revenue account and multiple cost accounts.

I've set up a straight table which shows the cost as a percentage of revenue got each group, based on a certain cost accounts only. To do this, I have the Account Group and Variable Cost Account dimensions included in the table, with the Variable Cost Account being ignored in the expression calculating the revenue at an  Account Group level.

MonthAccount GroupVariable Cost AccountRevenueCostMargin %
Sep 2020ITIT Material Cost31,500.003,100.009.84%
Sep 2020ITIT Staff Cost31,500.0012,000.0038.10%
Sep 2020IT-31,500.000.000.00%
Sep 2020ManufacturingManufacturing Material Cost27,000.001,700.006.30%
Sep 2020ManufacturingManufacturing Staff Cost27,000.006,200.0022.96%
Sep 2020Manufacturing-27,000.000.000.00%
Sep 2020MarketingMarketing Material Cost13,500.00700.005.19%
Sep 2020MarketingMarketing Staff Cost13,500.003,400.0025.19%
Sep 2020Marketing-13,500.000.000.00%

Leaving only the Margin % expression, the data shows what we need - certain cost categories as a percentage of revenue for their corresponding group:

MonthAccount GroupVariable Cost AccountMargin %
Aug 2020ITIT Material Cost10.77%
Aug 2020ITIT Staff Cost34.62%
Aug 2020ManufacturingManufacturing Material Cost6.25%
Aug 2020ManufacturingManufacturing Staff Cost25.00%
Aug 2020MarketingMarketing Material Cost5.00%
Aug 2020MarketingMarketing Staff Cost25.00%
Sep 2020ITIT Material Cost9.84%
Sep 2020ITIT Staff Cost38.10%
Sep 2020ManufacturingManufacturing Material Cost6.30%
Sep 2020ManufacturingManufacturing Staff Cost22.96%
Sep 2020MarketingMarketing Material Cost5.19%
Sep 2020MarketingMarketing Staff Cost25.19%

The challenge is that I'm trying to convert this last table to a line chart, to show the percentage trend per Variable Cost Account over months. When doing this, the Account Group dimension is included and its data points skew the chart, as it has 3 dimensions.

However, if I remove it, the expression totals will not be correct.

Any advice on how I can get the line chart to display the values from the straight table, in line chart format only?

I have attached a sample app which shows the issue and has the 2 objects, to which I'm referring.

Thanks,

Dod

Labels (3)
1 Solution

Accepted Solutions
edwin
Specialist III
Specialist III

try this expression:

= aggr(nodistinct Sum({<[Cost Type]={'Variable'}>}[Cost Total]),[Account Group],Month, [Variable Cost Account]) /
aggr(nodistinct Sum([Revenue Total]), [Account Group],Month)

this is the straight table:

edwin_0-1605144371257.png

 

if converted to line:

edwin_1-1605144430917.png

 

 

View solution in original post

6 Replies
villegasi03
Creator
Creator

Still trying to wrap my head around this but something tells me you might want to look at using the TOTAL statement in your margin % column. If I am not mistaken that would be in the revenue field. You could also look at using the aggr function. 

DOD
Contributor III
Contributor III
Author

Thanks for the response.

I have used the TOTAL statement in the Revenue element of the margin % expression:

=(Sum({<[Cost Type]={'Variable'}>}[Cost Total]))
/
Sum(Total<[Account Group],[Month]> [Revenue Total])

This works fine when presenting the data in a straight table. However, for it to be accurate, it does need the Account Group dimension to be part of the table.

When trying to display the data in a line chart, I need to remove the Account Group dimension as otherwise, it has 3 dimensions and the Variable Cost Account margin % values won't display on their own.

However, removing the Account Group dimension impacts the calculation.

I'm trying to figure out another way of calculating each Variable Cost Account as percentage of its Account Group revenue total, without needing to include the Account Group dimension.

Thanks again.

 

DOD
Contributor III
Contributor III
Author

I'm just checking if anyone has any advice on this.

The issue is shown in the .qvw file I attached.

Thanks,

Dod

Brett_Bleess
Support (Former)
Support (Former)

What may help the most is if you can provide a screenshot of how you want the line chart to actually appear, that is likely going to be most helpful in getting further responses on this.  The only place I could point you for other ideas would be the following two Community areas:

https://community.qlik.com/t5/Qlik-Design-Blog/bg-p/qlik-design-blog

https://community.qlik.com/t5/QlikView-Documents/tkb-p/qlikview-documents

Regards,
Brett

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.
edwin
Specialist III
Specialist III

try this expression:

= aggr(nodistinct Sum({<[Cost Type]={'Variable'}>}[Cost Total]),[Account Group],Month, [Variable Cost Account]) /
aggr(nodistinct Sum([Revenue Total]), [Account Group],Month)

this is the straight table:

edwin_0-1605144371257.png

 

if converted to line:

edwin_1-1605144430917.png

 

 

View solution in original post

DOD
Contributor III
Contributor III
Author

Thank you very much, @edwin . This has produced the output I needed. Your help is much appreciated.

 

@Brett_Bleess, I needed the line chart to display with only the Month and Variable Cost Account dimensions, but to retain the actual cost % totals from the table including the Account Group dimension. Thanks also for the guidance.