Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Announcements

You can succeed best and quickest by helping others to succeed. Join the conversation.

- Qlik Community
- :
- All Forums
- :
- QlikView App Dev
- :
- Set Analysis in pivot question

Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Murlimes

Contributor III

2020-11-10
01:32 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Set Analysis in pivot question

Hello,

Since i dont know how to explain this comprehensively i just try to give an example which should breakdown my problem in an easy way.

I have Sum(sales) for every day of a month in the actual select month in a pivot table. Now i want to add the sum(sales) of a specific day to every other day in the month. The amount can differ and is only available after its acutally calculated in the pivot table.

i.e. Sum(Sales) for the 15th October is 20. This is caclucalated dynamicly daily and can differ each load. Now i want add this 20 to every other in the month like:

sum(sales)+ sum(sales<15th october>) so if sales on 1st is 10 it would 10+20.

When i use set analysis , the pivot table will ignore the second part of the statement on any other day except the 15th , which seems logical, but how can i write the expression that it adds it to every day nevertheless?

is this possbile in a pivot, or do this need to be addressed in the load statement. I tried different combination of aggr/rangesum, but somehow it never works as intended. In general i want to calculate attrition and therefore want to refer to a specific day by demand around which i would base the other calculations in the pivot

Thanks

342 Views

1 Solution

Accepted Solutions

Vegar

MVP

2020-11-10
03:03 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

You might be helped by using total in your second sum () like this.

sum(sales)+ sum( **TOTAL** sales<15th october>)

Vegar

Qlik Community MVP

Qlik Community MVP

3 Replies

Vegar

MVP

2020-11-10
03:03 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

You might be helped by using total in your second sum () like this.

sum(sales)+ sum( **TOTAL** sales<15th october>)

Vegar

Qlik Community MVP

Qlik Community MVP

edwin

Master

2020-11-10
09:39 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

somewhat in line with what Vegar suggested, but with some preconditions:

assuming that you have a Month that the user selects to preselect all the DAYS in the month; + have a DAY field which is day(YourDate) - so you can use this in your set analysis.

then in your pivot, use Day as the dimension. for expression, use

sum(Amount) + sum(total {<Day=15>}Amount)

the first chart shows sum(Amount), 2nd chart shows sum(total {<Day=15>}Amount), 3rd shows the final expression

297 Views

Murlimes

Contributor III

2020-11-11
02:07 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

it works, thx a lot guys.

281 Views