Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Highlighted
Yury
New Contributor

variable calculation in set analysis

Hi Community, 

I have Pivot table . I made dimension in rows and i see exact value that I need (number 1).

=aggr({distinct}[Period (Discount)],[Business Unit],[Country (Discount)])

Then I'm trying to add this value to [BILL AMT] measure calculation to sum amount with dates filtered by from and to using addmonths function like:

=Sum(
{$<[Creation date (billing)]=
{">=$(=addmonths (vDateEnd,-aggr({distinct}[Period (Discount)],[Business Unit],[Country (Discount)]) ))<=$(=Date(vDateEnd))"}>} 
[BILL AMT]
)

I want the "aggr()" part to be changed to 1. But it not happening... Chart ignores the statement and returns values like  =SUM([BILL AMT])

If I manually replace the "aggr()" with 1 I will get what I want, but this value should be dynamic (base on loaded data)...

=Sum(
{$<[Creation date (billing)]=
{">=$(=addmonths (vDateEnd,-1 ))<=$(=Date(vDateEnd))"}>} 
[BILL AMT]
)

I've already spent many days finding what exactly i can do and now I'm facing this issue.

Thank you in advance!

Yury.

2019-04-03_23-31-00.png

3 Replies
vikasmahajan
Esteemed Contributor

Re: variable calculation in set analysis

Can you attach sample demo with app. Have you create a calendar in your model ?

 

Vikas

vikasmahajan
Esteemed Contributor

Re: variable calculation in set analysis

Also mention clearly what you want to achieve
Yury
New Contributor

Re: variable calculation in set analysis

Thank you for your response.
Unfortunately, I won't be able to attach demo, as it's pretty complex model made by our vendor.
As I found out there is no chance to make variable calculated taking dimensions in consideration.
So now I'm stuck figuring out on how to make calculation for different rows in set analysis...

vDateEnd is made via calendar and not connected to model (it's just max(selectedDate)),
I want the part
aggr({distinct}[Period (Discount)],[Business Unit],[Country (Discount)])
to be changed dynamically to 1, 3, 6 or 12 depending on row ([Business Unit],[Country (Discount)])).
[Country (Discount)] 1 - 1 [Country (Discount)]
[Country (Discount)] 1 - n [Business Unit]
[Country (Discount)] & [Business Unit] 1 - 1 [Period (Discount)]
[Period (Discount)] is coming from excel spreadsheet and it different for [Business Unit], [County (Discount)].
[cid:image002.png@01D4EB9E.BB8B5DA0]


[cid:image001.png@01D4EB9D.40292C60]