Skip to main content
Announcements
Qlik Connect 2024! Seize endless possibilities! LEARN MORE
cancel
Showing results for
Search instead for
Did you mean:
Contributor III

## if first day of month..

*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.

Labels (1)
• ### General Question

1 Solution

Accepted Solutions
Contributor III
Author

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 🙂

8 Replies
Former Employee

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

Contributor III
Author

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

Former Employee

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
;

Contributor III
Author

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

Former Employee

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

Contributor III
Author

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..

Former Employee

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.

Contributor III
Author

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 🙂