Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Become an analytics expert with Qlik's new 15 week course: Applied Data Analytics using Qlik Sense. READ MORE
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)