topic Re: straight table in QlikView

straight table

woshua5550 — Wed, 25 Nov 2020 16:16:04 GMT

Hello community

plz look at my sample data first

"A Salon" ,"A Class" and "A Class New" , they are actually the same cluster with different name in different year

I want create a straight table which shows the "newest" name according to year selection , and compare sales between current year and last year

so when I select 2017 , it shoud be

and for 2016 , it should be

with 2015 , it should be

is this possible ? you can create a dimension table to do this but please do not change this fact table

plz give me a hand , thanks in advance !

Re: straight table

Anil_Babu_Samineni — Wed, 28 Jun 2017 15:44:32 GMT

I think you need 2 expressions to achieve this

Create straight table and dimension as Cluster

Sum({<Year = {'$(=Max(Year))'}>} Sales)

and Sum({<Year = {'$(=Max(Year)-1)'}>} Sales)

PS - I don't have Qlikview s/w with me

Re: straight table

sunny_talwar — Wed, 28 Jun 2017 16:05:29 GMT

May be try this

If(Year, Sum(TOTAL {<Year={$(=Year-1)}>}Sales))

Re: straight table

antoniotiman — Wed, 28 Jun 2017 16:16:48 GMT

An other way

Dimensions :

=FirstSortedValue(TOTAL Cluster,-Year)

Year

Expression :

Sum({<Year={'>=$(=Max(Year)-1)<=$(=Max(Year))'}>} Sales)

Regards,

Antonio

Re: straight table

woshua5550 — Thu, 29 Jun 2017 04:13:37 GMT

well done.. thanks Sunny

but actrually it not solved my problem.. sorry ,I'm not make it so clear

let me change my table into this:

"B Class" is totally different with "A Class" , so you can't sum up them together

in this case , when I select 2017 , I want to show

and for 2016

it is tricky, right...?

Re: straight table

sunny_talwar — Thu, 29 Jun 2017 08:39:03 GMT

Will it literally have New as the extra word in addition to the other text? I mean there has to be some way to know what is common between the rows to program it, right?

Re: straight table

sunny_talwar — Thu, 29 Jun 2017 08:56:50 GMT

May be check out the attached

Re: straight table

woshua5550 — Thu, 29 Jun 2017 08:56:56 GMT

well,you can maintain a mapping table to recognise which clusters are the same and which are not

in may case, "A Salon","A Class" and "A Class New" are the same cluster

"B Class" is another cluster

Re: straight table

antoniotiman — Thu, 29 Jun 2017 09:02:02 GMT

Hi Dave,

See Attachment

Regards,

Antonio

Re: straight table

woshua5550 — Mon, 03 Jul 2017 08:06:16 GMT

Thx a lot Antonio !

It's really a good idea to use firstsortedvalue function ,problem solved !