CODEWITHSUNDEEP
qlikviewqliksense
rosuandreimihai
rosuandreimihai

Reputation: 656

Qlik sense+restrict values in pivot table

I have the following data set (only that it's on a larger scale):

LOAD * INLINE [
    Region, Value, Quarter   
    A, 1, Q1 18
    B, 2, Q1 18
    C, 3, Q1 18
    D, 4, Q1 18 
    A, 1, Q1 17
    B, 2, Q1 17
    C, 3, Q1 17
    D, 4, Q1 17 
    A, 1, Q2 17
    B, 2, Q2 17
    C, 3, Q2 17
    D, 4, Q2 17
    A, 1, Q3 17
    B, 2, Q3 17
    C, 3, Q3 17
    D, 4, Q3 17
    A, 1, Q4 17
    B, 2, Q4 17
    C, 3, Q4 17
    D, 4, Q4 17
    A, 1, Q4 16
    B, 2, Q4 16
    C, 3, Q4 16
    D, 4, Q4 16       
];

I need to present the data in a pivot table, using Quarter as columns, Region as rows and sum(Value) as measure If I will create the pivot table like this, it will render all 6 quarters. What if I would like to restrict the quarters only for the last 4 of them? It needs to be a dynamic solution, as I will add data to the load, and always need to display the last 4 quarters

Can it be done by set expression? Or I need to use a logic in the load script? Honestly, I would rather go for a solution involving set expression

Could someone come up with a way to achieve that?

Upvotes: 1

Views: 695

Answers (1)

Vanbrabant Thomas
Vanbrabant Thomas

Reputation: 41

It will be complex to handle it with a single set analysis using your Quarter field.

One way to do it :

Data:
LOAD * INLINE [
    Region, Value, Quarter   
    A, 1, Q1 18
    B, 2, Q1 18
    C, 3, Q1 18
    D, 4, Q1 18 
    A, 1, Q1 17
    B, 2, Q1 17
    C, 3, Q1 17
    D, 4, Q1 17 
    A, 1, Q2 17
    B, 2, Q2 17
    C, 3, Q2 17
    D, 4, Q2 17
    A, 1, Q3 17
    B, 2, Q3 17
    C, 3, Q3 17
    D, 4, Q3 17
    A, 1, Q4 17
    B, 2, Q4 17
    C, 3, Q4 17
    D, 4, Q4 17
    A, 1, Q4 16
    B, 2, Q4 16
    C, 3, Q4 16
    D, 4, Q4 16       
];

Tmp:
LOAD Distinct 
    Quarter,
    Right(Quarter, 2) as Year,
    Left(Quarter, 2) as Q
Resident Data
;


Calendar:
LOAD *,
    RowNo() as SeqNumber
Resident Tmp
ORDER BY Year DESC, Q DESC;

DROP Table Tmp;

Then using the following expression : Sum({<SeqNumber={"<=4"}>} Value)

Upvotes: 1

Related Questions