# New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Highlighted
New Contributor

## Calculate Value for Reference Date

Hi,

we use a manually crafted table with specific reference dates (previous year) for every day and every business location/CC.

I'm able to map those reference dates onto my facts table (via 'key' "CC-Date"), but im not yet able to calculate the reference revenue in the same row as the actual revenue.

What I'm looking for:

 Date Revenue RefDate RefRevenue? Location/CC (i think you can ignore this field for answering the question) 01.01.2019 235135 01.01.2018 126513 (=Revenue from RefDate) A/1500 01.01.2019 218952 03.01.2018 161341 B/2300 02.01.2019 219925 03.01.2018 104465 A/1400

The difficulty comes with the underlying table consisting of rows that are based on the revenue per article:

 Date Revenue ArticleID Location/CC 01.01.2019 150 15 A/1500 01.01.2019 30 18 A/1400 01.01.2018 140 15 A/1500

The Articles are needed for filter purposes.

I'm running circles in my head, but i think the solution might be very simple.

Thanks!

Best Regards

Karl

Labels (2)

2 Replies
Partner

## Re: Calculate Value for Reference Date

Hi Karl,
Can you provide some demo data to look at.

Regards
Frank
New Contributor

## Re: Calculate Value for Reference Date

I attached a txt-file with tab as delimiter.

Thats how the source table looks like.

In the example file you can find three dates from 2019: 01, 02 and 03 May and the corresponding RefDates 01, (02) 03 and 04 May 2018.

In this case all 2019 dates have only one corresponding date in 2018, but it's also possible, that there is a different refdate for every CC.

I dont need CC and ArticleID in the final table, but i need to filter on them. On the Date-Dimension, I'm only filtering on the Date, not the RefDate.

I guess this result is only achievable via set analysis and some aggr() usage?! Thanks alot!