Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
LINKEDIN LIVE: Democratizing data to enhance customer-centricity. JULY 29TH REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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

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!

1 Solution

Accepted Solutions
jerem1234
Specialist II
Specialist II

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!

View solution in original post

7 Replies
jerem1234
Specialist II
Specialist II

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!

Not applicable
Author

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.

Not applicable
Author

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
Specialist II
Specialist II

I would create a month field in script like:

month(Week) as Month

Then my formula should work.

Hope this helps!

Not applicable
Author

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
Specialist II
Specialist II

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!

View solution in original post

Not applicable
Author

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!