chart to cycle between weekly and monthly (first week only) view

[#1]

Dear all,

I have some charts, which I need to display both in weekly and monthly details.

Lowest granularity of data is weekly.

I wanted to use the cyclic dimension, so to switch from week to month.

But (expectably enough), when I switch to month, QV is aggregating all the weekly data for that specific month.

Is there a way to just show the first instance of the month?

thanks!

[jerem1234]

You can probably achieve this through a calculated dimension.

For a calculated dimension, you can try something like:

=aggr(only({<Week ={'=aggr(rank(-Week), Month, Week)=1'}>}Week), Week)

Please find attached for an example.

Hope this helps!

[#3]

Hi.

Did you define your group as Cyclic? I believe that it should be a Drill-Down Group, as Monthly and Weekly are Time Dimensions.

Regards,

BS.

[#4]

Dear Jeremy,

your example works cahrmingly!

but  sadly my data is a bit more " difficult"  than yours..

pls see attached example!

I have timestamps (and data is refreshed weekly) .

Perhaps is the use of the MonthStart function which is complicating things?

[jerem1234]

I would create a month field in script like:

month(Week) as Month

Then my formula should work.

Hope this helps!

[#6]

that' s an effective workaround!

but once I have a month field, wouldnt it be easier to use something like a set analysis with min(Month) or something like that?
Just wondering which would be the best approach to tackle this

[jerem1234 · Accepted solution]

You could add set analysis, but since you are using a group, the new formula would work for one of your groups, but since it is now restricting which week you want, it won't work for the other. You'd have to add an if statement to it like:

if(GetCurrentField([New Group2]) = 'Week', sum(Value), sum({<Week ={'=aggr(rank(-Week), Month, Week)=1'}>}Value))

to make it work, but by then the calculated dimension is probably easier to use.

You could also create a flag in script like:

if(Monthstart(Week)<>previous(Monthstart(Week)), 1, 0) as FirstWeekFlag

Then this would reduce your set analysis a bit to just:

=aggr(only({<FirstWeekFlag={1}>}Week), Week)

If you have a large data set and are worried about performance, I would try to move as much as you can into the script (such as the new month field or the flag if you need). Then this would reduce the amount you have to calculate for the charts. If not, I would go with the calculated dimension, since then you won't need the if statement for your expression.

Please find attached for examples.

Hope this helps!

[#8]

Thank you for all the detailed information jeremy.

indeed my data set is large or bound to become large, hence I was having the intention to move as much as possible to the script.

I am actually implementing the Month field in-script and using the calculated dimension

thank you again for the great help!