Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have the following data format in a straight table where all fields are either dimensions or calculated dimensions,
ID | Name1 | Name2 | Tier |
---|---|---|---|
10 | Chicago | NYC | White |
10 | Chicago | Chicago | Yellow |
10 | Chicago | Boston | Silver |
10 | Chicago | Seattle | Brown |
In the above table ID field and Name1 remain same for an ID selection, in this case being 10. Only the ID is selected and hence the table gets populated based on ID selection. The tier information is based on the corresponding Name2 value.
My question is for a given selection of ID(which has many values) i will need to restrict the tier information as follows,
I am kind of stuck on this solution and would appreciate any help.
Thanks,
Santosh
May be like this
vVar
=If(Name1 = Name2, Tier)
and then this
If($(vVar) = 'Yellow', Tier,
If($(vVar) = 'Silver', If(Match(Tier, 'Silver', 'Brown', 'White'), Tier),
If($(vVar) = 'Brown', If(Match(Tier, 'Brown', 'White'), Tier),
If($(vVar) = 'White', If(Match(Tier, 'White'), Tier)))))
Can you explain your 2nd requirement not pretty clear sorry?
May be use calculated dimension for you first question Name2.
= Aggr(IF(Name1 = Name2, Name2), Name1)
Or as and Expression
= IF(Name1 = Name2, Name2) something like this.
So here you would want to see only Yellow?, but if for instance Tier was Silver, you would have wanted to see Silver and Yellow?
Apologies if that wasn't clear. What i meant was, based on the tier selected from the 1st condition in this example Yellow, i will need to show only those Name2 which have tiers equal to or lesser in terms of priority as follows,
Yellow>Silver>Brown>White
So in this case i will be showing all the tiers since Yellow has highest priority, but there might be some scenarios where brown might be selected wherein only Brown or white needs to eb shown. So this needs to be dynamic. Hope this helps
If its yellow, i will need to show all values lesser than or equal to in priority, so in this case Yellow, Silver, Bronze, White and in the case where the tier is Silver, should only show SIlver Bronze and N/A values.
And you will ever be looking at one ID at a time? Your users will never want to see more than one ID at any given point of time?
Only one ID is displayed in the table at all times at a time which is based on a user search criteria. The Name1 values are based on the ID and hence get populated accordingly, but the Name2 values will change based on the selected ID.
May be like this
vVar
=If(Name1 = Name2, Tier)
and then this
If($(vVar) = 'Yellow', Tier,
If($(vVar) = 'Silver', If(Match(Tier, 'Silver', 'Brown', 'White'), Tier),
If($(vVar) = 'Brown', If(Match(Tier, 'Brown', 'White'), Tier),
If($(vVar) = 'White', If(Match(Tier, 'White'), Tier)))))
Thanks Sunny for your suggestion. I was able to create the solution with your help. For the first part i created 2 variables 1 for holding the Name1 field value based on selected ID and another vOppName to hold the value of the Tier for the correspoding Name1 field as follows,
vOppName= =Maxstring({<Name2 = {'$(vName)'}>}Tier)
Once this was done it was just limiting the values based on priority like you have mentioned. But only difference, i used the following syntax
If(vVar = 'Yellow', Tier,
If(vVar = 'Silver', If(Match(Tier, 'Silver', 'Brown', 'White'), Tier),
If(vVar = 'Brown', If(Match(Tier, 'Brown', 'White'), Tier),
If(vVar = 'White', If(Match(Tier, 'White'), Tier)))))
Not sure why the $(vVar) which i usually use for variables did not work. Anyhow thanks for your help appreciate it
Very nice, I think the $(vVar) would have worked, if the variable was defined without a equal sign. I always get confused with the variables and tend not to use them. But, I am glad you were able to figure it out on your own.
Best,
Sunny