Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

karl
New Contributor II

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:

DateRevenueRefDateRefRevenue?Location/CC (i think you can ignore this field for answering the question)
01.01.201923513501.01.2018126513 (=Revenue from RefDate)A/1500
01.01.201921895203.01.2018161341B/2300
02.01.201921992503.01.2018104465

A/1400

 

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

DateRevenueArticleIDLocation/CC
01.01.201915015A/1500
01.01.20193018A/1400
01.01.201814015A/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
Partner

Re: Calculate Value for Reference Date

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

Regards
Frank
Highlighted
karl
New Contributor II

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!