Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Partner
Partner

Current Vs Previous Values

Hi,

I am creating Qliksense application.

The user wants to compare current and previous month values.

I have derived a table which contains each months data.

i would like to calculate the corresponding previous month data in the same table.

This is my input table. 

Key PreviousKeyItemAmount
COL1_COL2_1_Item1COL1_COL2_12_Item1Item11200
COL1_COL2_1_Item2COL1_COL2_12_Item1Item21100
COL1_COL2_2_Item1COL1_COL2_1_Item1Item12000
COL1_COL2_2_Item2COL1_COL2_1_Item1Item22100
COL1_COL2_3_Item1COL1_COL2_1_Item1Item13000
COL1_COL2_3_Item2COL1_COL2_1_Item1Item23200

 

I want to derive the output table like below

 

Key ItemAmountPrevious Amount
COL1_COL2_1_Item1Item11200-
COL1_COL2_1_Item2Item21100-
COL1_COL2_2_Item1Item120001200
COL1_COL2_2_Item2Item221001100
COL1_COL2_3_Item1Item130002000
COL1_COL2_3_Item2Item232002100

 

My key column is the composite key(concatenating multiple columns to form a key).

Please give me your valuable suggestion to achieve this.

Thanks,

John

 

2 Replies
mato32188
Contributor III

Re: Current Vs Previous Values

Hi John,

in load script editor follow these steps:

tmp:

LOAD

* ,

Subfield(Key,'_',3) as Month

from YOUR INPUT TABLE;

 

noconcatenate

Output:

LOAD

*, 

Peek(Amount,-2) as "Previous Amount"

resident tmp

order by Month,Item;

drop table tmp;

BR

Martin

Partner
Partner

Re: Current Vs Previous Values

Hi Angie,

You should Left Join the table with the previous key and previous amounts to the new key. This way you will have your result. 

Can you show some information from the key and previous key. Like:

Key PreviousKeyItemAmount
COL1_COL2_1_Item1COL1_COL2_12_Item1Item11200

To this key, 1200 is the amount, but what is the amount of the previous key. What corresponds to this and where can this information be found?

Jordy

Climber

Work smarter, not harder