topic Re: show this years and last years figures on same line in table in App Development

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

Davidoff — Sat, 16 Nov 2024 21:18:54 GMT

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

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

PrashantSangle — Mon, 04 Mar 2019 10:58:03 GMT

can you provide some sample data with expected output.

Regards,
Prashant sangle

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

Davidoff — Mon, 04 Mar 2019 11:08:28 GMT

Hi Prashant,

thanks for your reply. Does this help (attached)?

Thanks,

David

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

Davidoff — Mon, 04 Mar 2019 11:10:31 GMT

example data..

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

PrashantSangle — Mon, 04 Mar 2019 11:13:36 GMT

all fields are coming from single table or multiple table.
If it is coming from single table then move your logic in script.

Regards,
Prashant Sangle

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

Davidoff — Mon, 04 Mar 2019 11:18:49 GMT

Multiple tables.

I don't think i could move the logic into the load script; it needs to be dynamic, depending on what date range user selects. plus there are additional columns for stock, coming from another table. And the date island table of course.

Thanks,

David

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

Davidoff — Mon, 04 Mar 2019 12:19:40 GMT

guys / gals,

am i wrong here? Can the expressions be put in the load script when the date ranges are selected in the UI by the user? And these ranges select records in date island?

Happy to accept I'm being thick here 🙂

Thanks.

David

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

Gabriel — Mon, 04 Mar 2019 12:55:21 GMT

Hi Dave,

Please attache sample data. You can anonymous the data.

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

Davidoff — Mon, 04 Mar 2019 13:20:38 GMT

Hi Gabriel,

hows this?

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

Gabriel — Mon, 04 Mar 2019 14:01:13 GMT

Hi Dave,

Your filters in the expression has to be same except for vSelectedYears & vPrevSelectedYear. Try that and let me know

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

Davidoff — Mon, 04 Mar 2019 14:15:24 GMT

Hi Gabriel,

which expression? Can you elaborate?

Thanks,

David

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

PrashantSangle — Mon, 04 Mar 2019 15:56:40 GMT

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

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

Davidoff — Mon, 04 Mar 2019 16:31:07 GMT

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

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

PrashantSangle — Mon, 04 Mar 2019 16:40:05 GMT

No, it must be +1.

Regards,
Prashant

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

Davidoff — Tue, 05 Mar 2019 10:24:26 GMT

Ah yes, I see what you mean 🙂

Thanks.

David

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

Davidoff — Tue, 05 Mar 2019 12:01:41 GMT

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?

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

Davidoff — Tue, 05 Mar 2019 13:59:32 GMT

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.

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

Davidoff — Tue, 05 Mar 2019 15:32:42 GMT

Thanks all for your input 😉