show this years and last years figures on same line in table

[Davidoff]

Hi all,

I am displaying sales, profit etc in columns of a table and grouping the rows by Season. I am required to show the values for the previous year in the same row, but different columns.

Problem is, the previous year data shows in a different Season group, because I am looking at the previous years data. 

Season Group expression:

=AGGR(ONLY({$<F_Year_Week={"$(vSelectedYears)"}, WK={"$(vSelectedWeeks)"}>} StockSeason), StockSeason)

Example of one of previous years column expression:

Sum({$<SalesType={'Sale'}, FP_MD={'FP'}, F_Year_Week ={"$(vPrevSelectedYear)"}, WK={"$(vSelectedWeeks)"}>} Price)

All data selection is controlled by a date island and selections on screen are stored in the vSelectedYears & vSelectedWeeks variables. vPrevSelectedYear is derived from vSelectedYears.

App works fine, but shows the previous year info in a different season group.

Your expertise and any assistance is much appreciated.

David

[Davidoff]

Hi Gabriel,

which expression? Can you elaborate?

Thanks,

David

[PrashantSangle]

hi,

in script you can concatenate the same transaction table by using addyears() on transaction date.
Logic can be

consider following example
TestData:
Load Date,Product,Sales Inline [
Date,Product,Sales
1-Jan-2018,A,100
1-Feb-2018,A,200
1-Mar-2018,B,200
1-Apr-2018,B,150
];
concatenate
Load AddYears(Date,1) as Date,Product,Sales as Previous_Sale
Resident TestData;

Use Previous_Sale field to show Previous Year Sale of That Product.

Hope that help.

Regards,
Prashant Sangle

[Davidoff]

Hi Prashant,

I was hoping you wouldn't say that 

Yes I think I'll have to load in again and add last years sale price to each record.

Wouldn't AddYears be -1 though?

David

[PrashantSangle]

No, it must be +1.

Regards,
Prashant

[Davidoff]

Ah yes, I see what you mean 🙂

Thanks.

David

[Davidoff]

UPDATE:

To complicate matters, I have tried to union a second load of the Sales dataset, but there could be 0,1 or many records for the same ProductID and date 1 year ago. 

Load fails however I try to load it (SUM(Price), GROUP BY ProductID etc. Any other ideas Qliksters?

[Davidoff · Accepted solution]

So I have had to give in and load the complete Sales dataset a second time to try and resolve the issue.

This does feel like an inelegant solution given Qlik's power. I had hoped there would be some set expression magic I could use here. 

[Davidoff]

Thanks all for your input 😉