straight table

[woshua5550]

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 !

[Anil_Babu_Samineni]

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

[sunny_talwar]

May be try this

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

[antoniotiman]

An other way

Dimensions :

=FirstSortedValue(TOTAL Cluster,-Year)

Year

Expression :

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

Regards,

Antonio

[woshua5550]

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...? 

[sunny_talwar]

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?

[sunny_talwar]

May be check out the attached

[woshua5550]

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

[antoniotiman · Accepted solution]

Hi Dave,

See Attachment

Regards,

Antonio

[woshua5550]

Thx a lot Antonio !

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