Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Set Analysis Question

Hi All,

I have a retail sales table with transaction number, sales, article code, and promotion name. Each transaction has multiple rows. Sales information and promotion information are stored in different rows.

Transaction NoArticle CodeSalesPromo Name

147

P101

20

-
147G101-$5 off
148P10225-
148G103-Spend more get more


I'm trying to do a promotion analysis for specific promotions. For example, when I select "$5 off", all the transactions that contain this promotion will be selected, in this case Transaction No.147. And all the sales of this transaction - on promotion and not, will be summed up. The formula I'm using right now is Sum( {1<[Transaction Key]=P({<[Promo Name]=[Promo Name]>})>}[Net Sales]). And the result I'm getting is like below - all the sales are grouped to the null value of promo.

Promo NameSales

$5 off

0
-45

Ultimately, I would like to have a table of results like below, with the sales properly grouped by promotion name. Is this something able to be done in set analysis? Or I would have to change the data structure?

Promo NameSales

$5 off

20
Spend more get more25

Thanks,

Shirley

8 Replies
sunny_talwar

When you select "$5 off", you continue to see "Spend more get more"?

Anonymous
Not applicable
Author

Sorry my mistake, I have corrected the post. When selecting a specific promo, I only saw one promotion information in the table. However ultimately I would like to construct a table containing all the promotion information, with the sales properly grouped to each promotion name.

sunny_talwar

Try this

Only({1<[Transaction No] = P([Transaction No]), [Promo Name]>} Aggr(Sum(TOTAL <[Transaction No]> {1<[Transaction No] = P([Transaction No]), [Promo Name]>} [Sales]), [Transaction No], [Promo Name]))

No selection

Capture.PNG

With Selection

Capture.PNG

Anonymous
Not applicable
Author

Thanks for the reply Sunny! Could you explain the logic of the formula?

Only({1<[Transaction No] = P([Transaction No]), [Promo Name]>} Aggr(Sum(TOTAL <[Transaction No]> {1<[Transaction No] = P([Transaction No]), [Promo Name]>} [Sales]), [Transaction No], [Promo Name]))


Why did you use "Only" and "Total" here?


sunny_talwar

TOTAL is used to get the sum of sales by transaction no.

ONLY is used so that I can use the same set analysis outside of the Aggr() as it is used inside. Else the set analysis restrictions won't work

Anonymous
Not applicable
Author

I don't think the formula works. I selected BabyEvent, and this is the result I got. Maybe it's due the fact that multiple discount could associate with one same transaction ID? But I dont't know why there is also no sales showing for BabyEvent.

Capture.PNG

sunny_talwar

I have no idea what to tell you based on a screenshot... may be if you are able to share your qvf or a sample... I might be able to check it out.

Best,

Sunny

Anonymous
Not applicable
Author

Hello Sunny,

Thanks for all the help! I have shared some sample data. Hopefully it can give you a more clear view of the problem.

Shirley