how to apply selection from Month to Date field

[tomovangel]

Hello guys,

I have a really big analysis, with a lot of expressions with set analysis( around 150 expressions).

Which all have conditions like this "Sum({<Date = {">=$(=AddMonths(Min(Date), -1))<=$(=AddMonths(Max(Date), -1))"}>}SoldQuantity)"

So using this when the user selects date it gives him the previous month's or year results.

BUT, if the user selects a week, for example week number 26, or a month, for example June, the set analysis don't work. I know I can make set analysis based on week, for example if week number 26 is selected, the previous one is shown, but this will require a lot of time to add all these set analysis, and the application will grow with above 300 master measures.

In qlikview, I have surpassed this using triggers, but I don't know how to do this in Qlik Sense .

Question:

When the week number 26 is selected, the dates corresponding to this week, have to be automatically selected.

How to achieve this in Qlik Sense?

Best regards,

Angel Tomov

[MK9885]

Maybe add Week in your set expression?

Sum({<Date = {">=$(=AddMonths(Min(Date), -1))<=$(=AddMonths(Max(Date), -1))"}, Week>}SoldQuantity)

[OmarBenSalem]

u'll have to add week, in ur set analysis.

becasue when u select a week, qlik will only see that week, so he would not be able to revert back to the previous year..

as shahbaz suggested, this will work:

Sum({<Date = {">=$(=AddMonths(Min(Date), -1))<=$(=AddMonths(Max(Date), -1))"}, Week>}SoldQuantity)

u also need to add the other date filed u might use in ur selections

[tomovangel]

Okay my friend, thank you for the detailed description
So if i make like this Sum({<Date = {">=$(=AddMonths(Min(Date), -1))<=$(=AddMonths(Max(Date), -1))"}, Week,Month>}SoldQuantity)

Even if the user selects Month, it will show him LAST month sales, right?

[OmarBenSalem · Accepted solution]

Yes, please, put them before ur date condition; I mean:

Sum({< Week,Month,Date = {">=$(=AddMonths(Min(Date), -1))<=$(=AddMonths(Max(Date), -1))"}>}SoldQuantity)

Try it and tell me if it works

[tomovangel]

Lets say it works man 😉

Thanks

The problem is lying in my inside expression which is Addmonths(min(date) -1)

Because, if July has started in 02.07 and  ended on 30.07
and june starts in 01.06 and ends on 31.06, my formula will not catch calculations from 31, because its not the max date..

I will think of other solutions for this but Thank you again I understand why it works in your way
Greetings and thanks again

[1994]

mtd and ytd expression required like that if select 2024 year jun month then 1 to 26 june data is there then previous year expression also 2023 june 1 to 26 june data is required expression like that