Comparision of periods - free selection of current and prior period value

[Thomas_Qlik]

In simple terms I have financial date with the following fields:
Account, Balance, Period and Comparative Period
I want to compare data from different periods with each other and want to have flexibility which periods are to be compared.

My idea was:
Based on the selections in the filters "Current Period" and "Comparative Period" I would like to control the output in a visualisation
I have put the filters fields in separate states each and use the default state for the diagram.

The column header names in my visualisation are populated according to the selected values in the filters

In order to have the aggregated values per line I intended to use the below measure:
{<[Comparative Period]={"$(=GetFieldSelections("Comparative Period", ',' ,1, 'CompPeriod')"}>}sum([Period Value in Group Currency])/1000

Problem is, the summation does not work and I am wondering, if this is due to an error in my formula or because what I am aiming to achieve is not possible.

Thanks for any help with this matter!

Thomas

[Sayed_Mannan · Accepted solution]

Hi, 

Here's how you can modify your measure to compare the selected "Current Period" and "Comparative Period":

=Sum({<[Period] = {"$(=GetFieldSelections('Current Period'))"}>} [Period Value in Group Currency])/1000

This formula will sum the "Period Value in Group Currency" for the selected "Current Period". You can create a similar measure for the "Comparative Period".

If you want to compare the same periods from different years, you can use the following formulas:

// For Current Year to Date (CY YTD)
=Sum({<DATE_COLUMN = {">=$(=DATE(YearStart(TODAY()),'YYYY-MM-DD')) <=$(=DATE(TODAY(),'YYYY-MM-DD'))"}>} TOTAL)

// For Previous Year to Date (PY YTD)
=Sum({1<DATE_COLUMN = {">=$(=DATE((YearStart(AddYears(TODAY(),-1))),'YYYY-MM-DD')) <=$(=DATE(AddYears(TODAY(),-1),'YYYY-MM-DD'))"}>} TOTAL)

Replace DATE_COLUMN with your date field. These formulas will give you the sum of the total for the current year to date and the previous year to date, respectively.

Remember to adjust these formulas according to your data model and requirements.

[Thomas_Qlik]

Many thanks for pointing me in the right direction, Sayed!

This is the formula which did the trick for me in the end:

Sum({<[Period] = {"$(=GetFieldSelections("Period",',',1,'CurrPeriod'))"}>} [Period Value in Group Currency])/1000