topic Re: if first day of month.. in App Development

if first day of month..

GENClik60 — Thu, 15 Dec 2022 12:57:56 GMT

*english below*
Hallo zusammen,
ich brauche für meinen Bericht eine Abfrage in QlikView.

Wenn der erste Tag des Monats, dann sollen die Tage des Vormonats genommen werden.
Bsp: Tag: 01.12.2022 -> Werte von 01.11.2022 bis 30.11.2022

Ab dem zweiten Tag des Monats die Daten des aktuellen Monats heranziehen. Hierzu habe ich schon folgende Formel, die auch das gewünschte Ergebnis liefert:
=if(Month(MSDatum)=Month(Today()) AND Year(MSDatum)=Year(Today()),'Ja')

Vielen Dank im Voraus.

Gruß Genclik

Hello, everyone,
I need a query in QlikView for my report.

If the first day of the month, then the days of the previous month should be taken.
Example: Day: 01.12.2022 -> values from 01.11.2022 to 30.11.2022
From the second day of the month, use the current month's data. For this I already have the following formula, which also gives the desired result:
=if(Month(MSDatum)=Month(Today()) AND Year(MSDatum)=Year(Today()),'Ja')
Thanks in advance.

Re: if first day of month..

hic — Fri, 16 Dec 2022 08:33:42 GMT

So you want the same Euro to appear on two different rows in the chart? E.g. the value for 15.11.2022 should be included in BOTH the following dates: 15.11.2022 and 01.12.2022.

I don't think it is possible to do that using an expression in the UI dimension. (Maybe it is, but then the expression would be very complicated). So I would do it in the data model instead.

Let's assume that you have a master calendar called "Kalender" that has the fields "Datum", "Monat", etc. Then you can create a second table using the following Load statements:

AkkumulationsKalender:
Load
AddMonths(MonthStart(Datum),1) as AkkumulationsDatum,
Datum
Resident "Kalender";
Load
Datum as AkkumulationsDatum,
Datum
Resident "Kalender" Where MonthStart(Datum) <> Datum;

Then you can use the "AkkumulationsDatum" to do exactly what you want. See also about the As-Of table, which is similar: https://community.qlik.com/t5/Qlik-Design-Blog/The-As-Of-Table/ba-p/1466130

Re: if first day of month..

GENClik60 — Thu, 15 Dec 2022 14:25:28 GMT

hello,
thank you for your answer, but its not what i want.

I want if its the first day of month, then take the data from the previous month.

For example:
date 05.12.2022 -> data from 01.12, 02.12, 03.12, 04.12, 05.12
date 01.12.2022 -> data from 01.11, 02.11, 03.11, 04.11,....,30.11

Re: if first day of month..

hic — Thu, 15 Dec 2022 15:12:16 GMT

Try

AkkumulationsDatum:
Load * Where not (Datum = MonthStart(Datum) and Datum = AkkumulationsDatum);
Load
Datum - IterNo() + 1 as Datum,
Datum as AkkumulationsDatum
Resident Kalender
While Datum - IterNo() + 1 >= MonthStart(Datum-1)
Order By Datum
;

Re: if first day of month..

GENClik60 — Fri, 16 Dec 2022 07:25:18 GMT

hey hic,
is it possible to add the formula in the chart? In the View is a master calender but its in the hidden script because the script is external..
I have the field "MSDatum", which shows me the data over midnight.
I update the data in the view, e.g. date 16.12.2022 -> MSDate 15.12.2022
And then i need when date 01.12.2022 -> MSDate 30.11.2022; Then i need the data from MSDate from 01.11.2022 till end November (30.11.2022)

i hope i could explain it.
greetings GENClik

Re: if first day of month..

hic — Fri, 16 Dec 2022 08:34:50 GMT

You cannot have such a function on the dimension. The function would need to return multiple dates - for example

Function('15.11.2022') should return all dates between '15.11.2022' and '01.12.2022'. And that's not possible.

But you could try something along the following:
Dimension: MSDatum
Measure:
RangeSum(Above(total
Sum(Betrag),
If(MSDatum=MonthStart(MSDatum),1,0),
If(MSDatum=MonthStart(MSDatum),MSDatum-MonthStart(MSDatum-1),MSDatum-MonthStart(MSDatum)+1)
))

Re: if first day of month..

GENClik60 — Fri, 16 Dec 2022 09:25:09 GMT

i dont want the dates between '15.11.2022' and '01.12.2022'.
The return when date '01.12.2022' is should be the dates between 01.11.2022 - 30.11.2022.

Means if first day of month then take the dates the month before..

Re: if first day of month..

hic — Fri, 16 Dec 2022 11:10:53 GMT

Oh, yes you do.

You want a Euro that belongs to 15.11.2022 (transaction date) to appear in the sum of all dates between 15.11.2022 and 01.12.2022 (dimension dates). That's what I mean with a function that returns multiple dates. A single transaction date should be linked to multiple dimension dates.

But try using RangeSum(Above(...)) as measure. I think it'll work.

Re: if first day of month..

GENClik60 — Tue, 20 Dec 2022 13:09:40 GMT

hi,
i think i have a solution now. i ve changed the formula of the dimension to:

=if(month(Today()-1)=Month(MSDatum)
AND
(year(Today()-1)=year(MSDatum)) ,'Ja', 'Nein')

i hope this will work. (if its work i mark this post as solution)

thanks anyway hic 🙂