Announcements
cancel
Showing results for
Did you mean:
Contributor II

## How to get a time series while a date is picked? (SET Analysis)

Hi,

i have a fact table employee data and using including date like (MonthYear, Year, Month etc.). For my App i'm mainly working with a one selected MonthYear per time . Until today. Because i just want to know "how much in month feb 21".

I would like to extend my analysis to include time series and time comparisons. For hours I have been testing different calculus formulas. Unsuccessfully.

Formulae such as

1
=Sum({1<[MonthYear]={\$(vPrevoiusMonat)}>}[NumOfEmploye])

or

2

=Sum({1<MonthYear={ ">=2022" }>}{\$<#Company={ [#Company] }>}[NumOfEmploye)

or

The Problem: MonthYear is alawys selected (due to all other reports).

2nd Problem. If i work with the Identier "1" it coudl work, but i need the drill through the companies. At the 2nd  formula u see how i try to combine the identifier "1" und "\$", but it doesnt work. Formular 1 is working for e.g. Buttons, but not for visual comparsion like bar chart.  How to deal with single data points in visualisations?

I want to have time series in bar or line charts or monthly comparisons.

Whats is the best practise solution to my problem?

Labels (1)
• ### General Question

Champion III

You can base your calculations on the Date field as below

For example Consider monthyear March-22 is selected

Expression for Previous Month(Feb 22)

=Sum({<[Datefield]=

{">=\$(=Date(monthstart(max(Datefield),-1),'Yyy-mm-dd'))<=\$(=Date(monthend(max(Datefield),-1),'Yyy-mm-dd'))"}

}[NumOfEmploye])

Expression for YTD 2022

=Sum({<[Datefield]=

{">=\$(=Date(yearstart(max(Datefield)),'Yyy-mm-dd'))<=\$(=Date(monthend(max(Datefield),-1),'Yyy-mm-dd'))"}

}[NumOfEmploye])

https://community.qlik.com/t5/Qlik-Design-Blog/Dates-in-Set-Analysis/ba-p/1472511

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.