topic Re: How to set monthstart in QlikView

How to set monthstart

soniasweety — Sat, 16 Nov 2024 17:50:02 GMT

Hi All,

@MayilVahanan @Taoufiq_Zarra

I want to count the users from particular date.

I have Month filed aug,sep,oct. and users

here my month start is from 25th to 25th..

count(users) for current month 25th oct to 25Nov..like for previous months also.. how can I set monthstart from 25th ?

Re: How to set monthstart

MayilVahanan — Fri, 13 Nov 2020 05:29:41 GMT

Hi @soniasweety

Try like below.. Its one of the method

LET vMinDate = Floor(MakeDate(2020,1,1));
LET vMaxDate = Floor(Today());
LET vToday = $(vMaxDate);

TempCal:
LOAD
Date($(vMinDate) + RowNo() - 1) as TempDate
AUTOGENERATE
$(vMaxDate) - $(vMinDate) + 1;

MasterCalender:
Load
TempDate as Date,
Week(TempDate) as Week,
Year(TempDate) as Year,
if(Day(TempDate) <= 24, Month(TempDate), Month(AddMonths(TempDate,1))) as Month,
Month(TempDate) as OriginalMonth
Resident TempCal
Order By TempDate ASC;

Drop Table TempCal;

In front end,

Dim: Month

Exp: Count(users)

Re: How to set monthstart

soniasweety — Fri, 13 Nov 2020 05:48:56 GMT

thank you.

cant we handle in set analysis?

Re: How to set monthstart

MayilVahanan — Fri, 13 Nov 2020 06:06:59 GMT

Hi @soniasweety

If you are using Month Field as Dimension, then set analysis will not work because month will start from 1st to 31st. But in your case, month need to start from 25th . So output won't come as expected.

Ex:

Month , Date, Count(users)
Jan, 1/1/2020, 10

Jan, 31/1/2020, 20

If you are using Month as Dim, then Jan will consider upto 31/1/2020.. Not upto 25th.

Hope it helps

Re: How to set monthstart

soniasweety — Mon, 16 Nov 2020 15:07:58 GMT

@MayilVahanan
thank you understood. And working fine with your logic.
but I have other date field Date of training DOT this is considered as training completed date and completed month of it.

attached sample .. and expected output in text object.

Re: How to set monthstart

soniasweety — Mon, 16 Nov 2020 03:27:36 GMT

Hi @MayilVahanan any suggestions ?

@tresB

Re: How to set monthstart

MayilVahanan — Mon, 16 Nov 2020 04:00:21 GMT

Hi @soniasweety

Try like below for your scenario.

https://community.qlik.com/t5/QlikView-Scripting/Link-2-Date-Fields-in-1-Table-to-1-Master-Calendar/td-p/84165

Re: How to set monthstart

soniasweety — Mon, 16 Nov 2020 05:01:26 GMT

Thank you @MayilVahanan I will try and update you.