Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Live chat with experts, bring your API Integration questions. June 15th, 10 AM ET. REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
lejours88
Creator
Creator

Show Last Period Value

Hi,

I have a problem about showing last period value in pivot table.

Here is my data:

      

AreaSalesIDTeritoryDistrictWeekValue
A11220100116015
A112201001160310
A112201001160522
A11220100216045
A11220100216067
A1122010021602

10

I have two different district. And maximum week Of District 001 is 1605, on the other side maximum week of district 002 is 1606.

So i want to create a pivot table like this :

 

AreaSalesIDTeritoryDistrict=sum(Value)max(Week)Last Week Value
A1122010013716050
A1122010022216067

District 002 shown right value (7), but district 001 return zero value (0).

I'm using this expression on my "Last Week Value" column : =sum({<Week={$(=max(Week))}>}Value)

The result i expect is

 

AreaSalesIDTeritoryDistrict=sum(Value)max(Week)Last Week Value
A11220100137160522
A1122010022216067

Can anyone give me the solution ? I also attached my sample QVW file.

Thanks in advance.

5 Replies
Anil_Babu_Samineni

But, For 002 you are expecting 7 or 5 ??

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
lejours88
Creator
Creator
Author

Hi,

For 002 i expect 7, because i want to show value on week 1606 (last week of district 002)

Thanks

Anil_Babu_Samineni

Oh, Sorry i have not read properly. May be this?

FirstSortedValue(Value, Aggr(Week, Value))

Capture.PNG

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
lejours88
Creator
Creator
Author

Hi,

I have try your solution,

It works on my sample data.

But it is not working on my real data, unfortunately i can't share my data, because it's confidential.

Do you have another solution ?

Thanks in advance

Anil_Babu_Samineni

Atleast data need to reply. Would it possible sample data. Or

You may explain bit more

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)