- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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?
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi, I think this expression will return the expected values:
=Aggr(If(StartWeek=WeekYear, Count(distinct ShopId)),StartWeek,WeekYear)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi, I think this expression will return the expected values:
=Aggr(If(StartWeek=WeekYear, Count(distinct ShopId)),StartWeek,WeekYear)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
BI Consultant
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hello Ruben,
Thanks, that worked as expected. I should have thought about using Aggr.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hallo Barnaby
Thanks for the suggestion. That was indeed a path I was exploring, but Ruben's solution has the advantage of simplicity.