topic Re: Help with selecting a single row for an expression in QlikView

Help with selecting a single row for an expression

fcecconi — Wed, 01 Apr 2015 13:37:10 GMT

Here is what my measure table looks like

User_ID	Metric	Value
1	Sales	100
2	Sales	150
3	Sales	75
1	Cost	50

I am joining the measure table to another table on the user_id

I want to create a straight table and have a column for sales and cost for each user id.

Something like this

User	Location	Sales	Cost
Tom	Home Office	100	50
Steve	Field	150
Mary	Field	50

Thanks!

Re: Help with selecting a single row for an expression

robert_mika — Wed, 01 Apr 2015 13:44:10 GMT

Could you post your data?

Re: Help with selecting a single row for an expression

Gysbert_Wassenaar — Wed, 01 Apr 2015 13:44:30 GMT

Add User and Location as dimensions to your straight table and use as expressions:

Sales: sum({<Metric={'Sales'}>}Value)

Cost: sum({<Metric={'Cost'}>}Value)

Re: Help with selecting a single row for an expression

martinpohl — Wed, 01 Apr 2015 13:47:40 GMT

Hello!

Create a chart / straight table >>>next

then add your dimensions User and Location >>>next

add a formula sum({<Metric={Sales}>} Value)

and a second formula with the syntax a formula sum({<Metric={Cost}>} Value)

Then give both a label and maybe format the numbers

finish

Regards

Re: Help with selecting a single row for an expression

fcecconi — Wed, 01 Apr 2015 13:51:02 GMT

Thanks Martin

Re: Help with selecting a single row for an expression

fcecconi — Wed, 01 Apr 2015 14:00:44 GMT

Gysbert

I was trying what you suggested, but everything was summing up to be the same value. What I noticed was that I had a loop in my model. Once I removed the loop, everything worked as expected..