Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Seier-Alsenz
Contributor II
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)
1 Reply
vinieme12
Champion III
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.