topic Re: Set expression referring to the current dimension - no result in App Development

Set expression referring to the current dimension - no result

JurgenW — Sat, 13 Jul 2024 11:52:14 GMT

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?

Re: Set expression referring to the current dimension - no result

rubenmarin — Sun, 14 Jul 2024 08:46:09 GMT

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

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

Re: Set expression referring to the current dimension - no result

barnabyd — Sun, 14 Jul 2024 21:37:52 GMT

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.

Re: Set expression referring to the current dimension - no result

JurgenW — Mon, 15 Jul 2024 12:13:42 GMT

Hello Ruben,

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

Re: Set expression referring to the current dimension - no result

JurgenW — Mon, 15 Jul 2024 12:14:49 GMT

Hallo Barnaby

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