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

Seier-Alsenz — Mon, 23 May 2022 10:05:09 GMT

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])

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

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?

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

vinieme12 — Mon, 23 May 2022 11:19:38 GMT

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

topic Re: How to get a time series while a date is picked? (SET Analysis) in App Development

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

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