Set expression referring to the current dimension - no result

[JurgenW]

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?

[rubenmarin · Accepted solution]

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

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

[barnabyd]

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.

[JurgenW]

Hello Ruben,

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

[JurgenW]

Hallo Barnaby

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