topic Re: add a target on the pivot table in QlikView

add a target on the pivot table

n1ef5ng1 — Thu, 05 Oct 2017 03:30:05 GMT

is it possible to add a target (customized column) on pivot table in qlikview.

see attached.

i would like to add a hard coded 'target' in between Date and the city.

do note that i designed in a way that i need > 1 expression in the table. (i could have add city on dimension and expression to be sum(sales) but it doesnt work in this way).

let me know if it makes sense

Re: add a target on the pivot table

n1ef5ng1 — Thu, 05 Oct 2017 06:29:00 GMT

any body? i am stucked in this for weeks ;(

Re: add a target on the pivot table

nehasaxena — Thu, 05 Oct 2017 06:48:05 GMT

Why you cant add a third expression in pivot table as target. It should work.

BR,

Neha

Re: add a target on the pivot table

n1ef5ng1 — Thu, 05 Oct 2017 06:55:16 GMT

cant....

Re: add a target on the pivot table

balar025 — Thu, 05 Oct 2017 06:55:41 GMT

Hello Ben,

Please find attached application.

Re: add a target on the pivot table

techvarun — Thu, 05 Oct 2017 06:56:49 GMT

Try the attached

Re: add a target on the pivot table

n1ef5ng1 — Thu, 05 Oct 2017 07:04:42 GMT

apology everyone, i should stated it clearly on what i need. but i feel is getting there.

please see the image on the expected output that we need.

Re: add a target on the pivot table

techvarun — Thu, 05 Oct 2017 07:13:02 GMT

check the attached

Re: add a target on the pivot table

n1ef5ng1 — Thu, 05 Oct 2017 07:16:57 GMT

thanks varun. i understand the idea and appreciate it. however my data model are quite complex to add in two rows on target to the data... is there any other way to do on not impacting the data model / excel sheet.

Re: add a target on the pivot table

pradosh_thakur — Thu, 05 Oct 2017 07:19:24 GMT

Please find the attached

i have done that using pick function. Let me know if you need anything.

regards

Pradosh

Re: add a target on the pivot table

pradosh_thakur — Thu, 05 Oct 2017 07:31:54 GMT

did it work for you?

Re: add a target on the pivot table

n1ef5ng1 — Thu, 05 Oct 2017 09:32:37 GMT

Thanks for the answer.

however i realized that it doesnt work if i have another dimension (say Continent) and each of the continent have their own sales target.

i tried to use pick and if (continent = 'asia','30',if(continent = 'antartica','100',.......~~~ but it crashes the whole QV

Re: add a target on the pivot table

techvarun — Thu, 05 Oct 2017 09:52:18 GMT

Check the attached,

Continent Added

Re: add a target on the pivot table

pradosh_thakur — Thu, 05 Oct 2017 09:55:26 GMT

it will work. the trick here lies with dim . update the .qvw with continent data. will do it and post again .

edit:

the same is already updated by varun with continent added.

you have to increase the dim to the number of dimension you are adding. It was 2 before and now 3. modify the set analysis a little bit and it will work.

check the above QVW posted by varun.

regards

Pradosh

Re: add a target on the pivot table

n1ef5ng1 — Thu, 05 Oct 2017 10:03:22 GMT

my continent is not a hard coded field and is on my dimension.

by using that i might have two dimensions with same continent...

like my field should have

Dimension : Date and Continent

Expression : sum(Amt) by country

target is by continent, it should read the dimension input of continent and display the target

Re: add a target on the pivot table

pradosh_thakur — Thu, 05 Oct 2017 10:05:33 GMT

kindly post the QVW. Let me try and get back to you.

regards

Pradosh

Re: add a target on the pivot table

n1ef5ng1 — Thu, 05 Oct 2017 10:07:04 GMT

See attached

i know it doesnt make sense for continent, maybe say.... product? product of the item... Asia should be Toy and Other should be Fashion. each of the product category should have individual target

Re: add a target on the pivot table

pradosh_thakur — Thu, 05 Oct 2017 10:39:23 GMT

please find the updated data with product added and same is done to the qvw.

the only change you need to do is drag the product column in its proper place by pressing ctrl+shift.

regards

Pradosh

Re: add a target on the pivot table

n1ef5ng1 — Thu, 05 Oct 2017 10:51:37 GMT

thanks! are you able to make it that Product A Target is 30 and Product B target is 50. that will solve my problem.

Re: add a target on the pivot table

pradosh_thakur — Thu, 05 Oct 2017 11:08:10 GMT

Find the attached.

now the value are 30 and 50 for A and B . If you have any specific value you can add that in the data model and associate it as well.

regards

Pradosh