Skip to main content
Announcements
Accelerate Your Success: Fuel your data and AI journey with the right services, delivered by our experts. Learn More
cancel
Showing results for 
Search instead for 
Did you mean: 
JurgenW
Contributor II
Contributor II

Set expression referring to the current dimension - no result

I have a table with a list of shops and the week they started working with us (StartWeek)

I now want to make a chart showing per week how many shops started.

Because the shops need to be linked to orders and the orders to my calendar, I cannot link this week to the calendar. But I want to use the calendar as a dimension so it responds to the selections the user makes.

So basically I want to count the number of shops where the 'StartWeek' of the shop equals the 'WeekYear' in the calendar.

After some research and trial and error I know that this formula calculates the correct 'WeekYear' value for every week in my dimension. In the chart I see a bar for every week with the WeekYear as a value (see also the attached file)

=Only({1<WeekYear=P(WeekYear)>}WeekYear)

And I know that the below formula calculates the correct number of shops for example the first week of 2024.

=Count({<StartWeek={"202401"}>}Distinct ShopId)

So I expected the this formula to give the correct result for every week but it doesn't. All values are zero.

=Count({<StartWeek={"=Only({1<WeekYear=P(WeekYear)>}WeekYear)"}>}Distinct ShopId)

If I replace the 'Only' by a 'Min' or 'Max' it returns all shops for which the StartWeek is filled.

Can anybody help me to get the correct formula? Or do you see another way to get this result without linking the shops to the calendar?

Labels (1)
1 Solution

Accepted Solutions
rubenmarin

Hi, I think this expression will return the expected values:

=Aggr(If(StartWeek=WeekYear, Count(distinct ShopId)),StartWeek,WeekYear)

View solution in original post

4 Replies
rubenmarin

Hi, I think this expression will return the expected values:

=Aggr(If(StartWeek=WeekYear, Count(distinct ShopId)),StartWeek,WeekYear)

barnabyd
Partner - Creator III
Partner - Creator III

I think you might need to use the Canonical Date design pattern. See this article ...

    https://community.qlik.com/t5/Design/Canonical-Date/ba-p/1463578

Cheers, Barnaby.

Barnaby Dunn
BI Consultant
JurgenW
Contributor II
Contributor II
Author

Hello Ruben,

Thanks, that worked as expected. I should have thought about using Aggr.

JurgenW
Contributor II
Contributor II
Author

Hallo Barnaby

Thanks for the suggestion. That was indeed a path I was exploring, but Ruben's solution has the advantage of simplicity.